Max Record by joining all multiple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max Record by joining all multiple tables

Author

Topic

Megha16
Starting Member

4 Posts

Posted - 2011-05-25 : 16:46:23

Hello All , I have been struggling with this problem since two weeks now , it doesnt seem to give me highest record by joining all tables , here is all of my tables:

TABLE tbl_Candidate :
Candidate_Id | Field1 | Field2

TABLE tbl_Files :
File_Id | Candidate_Id | FileVersion (ID from tbl_Candidate Table)

TABLE tbl_cells :
PTId | File_Id | PTField1 | PTField2 (FId from tbl_Files)

Desired Result : I want to get all record from all three tables where tbl_files column FileVersion has maximum value but it should be grouped by per record not just one single maximum value of FileVersion, it means wherever file_id matches with file_id in tbl_cells all records should be taken from tbl_cells with highest value of FileVersion from tbl_files by satisfying some condition in tbl_candidates.

Now here is my query :

select tbl_cells.PTField1, tbl_candidates.Field1,
tbl_files.FileVersion=(select max(tbl_files.FileVersion) from tbl_files
where tbl_files.id=tbl_cells.file_id ) as latestversion
from tbl_files
INNER JOIN tbl_cells ON tbl_files.id=tbl_cells.file_id
INNER JOIN tbl_candidates ON tbl_candidates.id=tbl_files.candidate_id
where tbl_files.file_type_id=4 AND
tbl_candidates.Field= some condition AND
tbl_candidates.Field2 is not null AND
tbl_files.id=tbl_cells.file_id ;

Result : This query returns me all FileVersion for all records not the highest version for each record.

Can you guys please help me , I am going crazy with this.

Thanks all !!!!!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 19:39:55

Can you post some sample input data and the corresponding expected output? That would make it much esier for someone who wants to respond.

Andrew Zwicker
Starting Member

11 Posts

Posted - 2011-05-25 : 19:48:09

Hi, I'm not 100% sure I've fully understood the problem, so apologies if this doesn't fully solve the issue. Hopefully it does, and at the least it can serve as a starting point.

Let's start with some dummy data. These column names might be slightly different than what you have...


create table tbl_Candidate(Candidate_Id int, field1 varchar(20), field2 varchar(20))
insert into tbl_Candidate values(1, 'AAAAA', 'BBBBB')
insert into tbl_Candidate values(2, 'CCCCC', 'DDDDD')
insert into tbl_Candidate values(3, 'EEEEE', 'FFFFF')
insert into tbl_Candidate values(4, 'GGGGG', 'HHHHH')

create table tbl_Files(File_Id int, Candidate_Id int, file_type_id int, FileVersion int)
insert into tbl_Files values(1, 1, 4, 1)
insert into tbl_Files values(2, 1, 4, 2)
insert into tbl_Files values(3, 2, 4, 1)
insert into tbl_Files values(4, 2, 4, 2)
insert into tbl_Files values(5, 2, 4, 3)
insert into tbl_Files values(6, 3, 4, 1)
insert into tbl_Files values(7, 4, 4, 2)

create table tbl_cells (PTId int, File_Id int, PTField1 varchar(20), PTField2 varchar(20))
insert into tbl_cells values(1, 1, 'MM', 'NN')
insert into tbl_cells values(2, 2, 'OO', 'PP')
insert into tbl_cells values(3, 2, 'QQ', 'RR')
insert into tbl_cells values(4, 2, 'SS', 'TT')
insert into tbl_cells values(5, 3, 'UU', 'VV')
insert into tbl_cells values(6, 3, 'WW', 'XX')
insert into tbl_cells values(7, 4, 'YY', 'ZZ')
insert into tbl_cells values(7, 5, 'AAA', 'EEE')
insert into tbl_cells values(7, 5, 'BBB', 'FFF')
insert into tbl_cells values(7, 6, 'CCC', 'GGG')
insert into tbl_cells values(7, 7, 'DDD', 'HHH')

To get the max file version for each candidate I'm using a derived table. I join on the files, and then I join on that derived table so I'm only getting the files with the max version. I then proceed to join to get the cells for that file.


-- Candidate 1 max file is 2, Candidate 2 max file is 3, etc.  Add the conditions as appropriate to get the cells for only 1 candidate
select cand.Candidate_Id, files.FileVersion, cells.* From tbl_Candidate cand 
inner join tbl_Files files on files.Candidate_Id = cand.Candidate_Id
inner join(select Candidate_Id, max(FileVersion) as 'maxVersion' 
from tbl_Files group by Candidate_Id) maxFileValues
	on maxFileValues.Candidate_Id = files.Candidate_Id 
    and files.FileVersion = maxFileValues.maxVersion
inner join tbl_cells cells on files.File_Id = cells.File_Id
where 
files.file_type_id=4 AND
--cand.Field1 = 'CCCCC' AND
cand.Field2 is not null

I hope this helps.

- Andrew Zwicker

Visit http://www.helpwithsql.com

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-25 : 21:31:51

I have a stored procedure, using an input variable (which works just fine), but I want to put some extra functionality to it.

When I execute the stored procedure, I want it to verify if the input variable matches another condition in the row.

So, when I enter '2' AS an emp_id (variable), I want it to verify if the given employee in the row contains the value 'Sales' under the column 'dept_name' of that same table.

If it does, execute the 'SELECT statement' (which I already have working).
If it doesn't, show message ('print statement').

I can do this with the 'if - else' keywords, but I don't know what criteria to enter to verify this.

For now, I have:

Please post real DDL. Learn to use ISO-11179 rules for the data element names, avoid needless dialect AND use ISO-8601 temporal formats, codes AND so forth. People cannot read your mind, so post your code AND clear specs if you really want help.

You mis-named the tables, did not declare the columns (which are not fields) AND gave no sample data. There is no magic “id” column in RDBMS that changes from table to table, etc. Rows are not not records. You have no DRI actions.

Here is my attempt to fix this nightmare.

CREATE TABLE Candidates
(candidate_id INTEGER NOT NULL PRIMARY KEY,
field1 INTEGER NOT NULL,
field2 INTEGER NOT NULL);

CREATE TABLE Files
(file_id INTEGER NOT NULL,
file_version INTEGER NOT NULL,
PRIMARY KEY (file_id, file_version),
candidate_id INTEGER NOT NULL
REFERENCES Candidates (candidate_id),

Note that the version of a file is part of the PRIMARY KEY

CREATE TABLE Cells
(pt_id INTEGER NOT NULL PRIMARY KEY,
file_id INTEGER NOT NULL,
file_version INTEGER NOT NULL,
FOREIGN (file_id, file_version)
REFERENCES Files (file_id, file_version)
pt_field1 INTEGER NOT NULL,
pt_field2 INTEGER NOT NULL);

Let's try again, but with SQL and not your vague private language

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-25 : 21:37:13

Megha16
Starting Member

4 Posts

Posted - 2011-05-26 : 14:58:08

Ok , my apologize if my question was not clear :
Andrew Thanks for reply , and I tried your query but didnt work.

Here is my tables structure:

Create table Tbl_candidates(Candidate_Id Integer Primary Key , Candidate_Letter Varchar(10) Not Null, Candidate_Primary Integer Not Null);
Insert into tbl_candidates(1(candidateid),'A'(candidateletter),1(primaryvalue))
Insert into tbl_candidates(2,'B',1)
Insert into tbl_candidates(3,'C',1)

Create table Tbl_Files(File_Id Integer Primary Key, Candidate_Id Integer Foreign Key (candidate_id from Tbl_Candidates) ,
File_Version Integer Not Null ,File_Type Varchar(15) Not Null);
Insert into Tbl_Files(11(fileid),2(candidateid),1(fileversion),D1(filetype))
Insert into Tbl_Files(22,2,1,D1)
Insert into Tbl_Files(33,2,1,D1)
Insert into Tbl_Files(44,2,2,D1) -> This is max fileversion
Insert into Tbl_Files(55,2,2,D1)
Insert into Tbl_Files(66,2,2,D1)

Create table Tbl_Cells(Cell_Id Integer Primary Key, CellName Varchar(150), File_Id Foreign Key (File_Id from Tbl_Files), File_Type Varchar,SectorId Integer , SectorId Integer) ;
Insert into Tbl_Cells(1(cellid),'Alex'(cellname),11(fileid),D1(filetype),111(sectorid))
Insert into Tbl_Cells(2,'Barbara',22,D1,112)
Insert into Tbl_Cells(3,'Chris',33,D1,113)
Insert into Tbl_Cells(4,'Don',44,D1,111)
Insert into Tbl_Cells(5,'Jane',55,D1,112)
Insert into Tbl_Cells(6,'Ronny',66,D1,113)

The result I want is :

Select tbl_files.fileid, tbl_cells.cellname, tbl_cells.sectorid, tbl_files.fileversion
where tbl_candidates.primary=1 ;
i.e.

44, Don,111,2
55,Jane,112,2
66,Ronny,113,2

How : From tbl_candidates , candidate_id 2 matches with candidate_id in tbl_files but tbl_files has 2 versions for candidate_id-2 and they are 1 & 2 with 6 rows but I want max of 1 & 2 which is 2,now file_id with highest fileversion 2 is 44,55,66 and this match with file_id in tbl_cells so it should pull only those rows from tbl_cells .
Basically each record in tbl_cells has three rows becuase it is tied up with sector id

I know it is complicated but I appreciate any help

Thanks all again !!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 15:48:15

Thanks Megha, your sample data and description makes it very clear what you want to accomplish. Here is my attempt at doing it. I hope you are on SQL 2005 or higher, otherwise this won't work:

;WITH cte AS
(
   SELECT candidate_id,MAX(file_version) AS File_Version 
   FROM Tbl_Files
   GROUP BY candidate_id
)
SELECT
   tc.File_ID,
   tc.CellName,
   tc.SectorId,
   tf.File_Version
FROM
   cte c
   INNER JOIN Tbl_Files tf ON
      tf.Candidate_Id = c.candidate_id AND tf.file_version = c.File_Version
   INNER JOIN Tbl_Cells tc ON
      tf.[File_Id] = tc.[File_Id]
WHERE
   tf.Candidate_Id = 2

I was not able to actually test this code - it would be easier to test if the script you post can simply be copied to a SQL window and run to generate the tables and sample data.

Megha16
Starting Member

4 Posts

Posted - 2011-05-26 : 16:19:10

Hi Sunita,

Thanks for reply but I guess your code was not completely displayed , It was half though.
I am using MySQL 5.1

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-26 : 17:43:51

I don't think the code that I posted would work on MySQL. I am not familiar with MySQL at all. This forum is a Microsoft SQL forum, so while there may be some people who are familiar with MySQL, your chances of getting faster and better responses would be much higher in a MySQL forum.

aravindram
Starting Member

1 Post

Posted - 2011-05-29 : 12:08:17

Hi Megha,

Please find below the answers for your query

Quote

select a.file_version,b.candidate_id,a.file_type,a.file_id,b.candidate_letter,b.candidate_primary,c.cellname
from tbl_Files1 a,tbl_Candidates b,tbl_cells1 c where a.file_version = (select max(file_version) from tbl_Files1 )
and a.candidate_id=b.candidate_id and a.file_id=c.file_id

Unquote

I hope your expexting the above result.

Please revert in case of any clarification required

Rgds
AR

Rgds
Aravind
Email :aravindramragupathy@yahoo.in

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-05-29 : 20:50:03

Again, let's post DDL and not this dialect and use ISO-11179 data element names. I also do not think this thing is designed correctly. Shouldn’t (file_id, file_version) be a key?

CREATE TABLE Candidates
(candidate_id INTEGER NOT NULL PRIMARY KEY,
candidate_letter VARCHAR(10) NOT NULL,
candidate_primary INTEGER NOT NULL);

---primary what? Primary is an adjective and needs a noun

INSERT INTO Candidates
VALUES (1, 'A', 1), (2, 'B', 1), (3, 'C', 1);

CREATE TABLE Files
(file_id INTEGER NOT NULL PRIMARY KEY,
candidate_id INTEGER NOT NULL
REFERENCES Candidates(candidate_id),
file_version INTEGER NOT NULL,
file_type CHAR(2) NOT NULL);

-– a version number is usually part of a key

INSERT INTO Files
VALUES (11, 2, 1, 'D1'),
(22, 2, 1, 'D1'),
(33, 2, 1, 'D1'),
(44, 2, 2, 'D1'), -> This is max file version
(55, 2, 2, 'D1'),
(66, 2, 2, 'D1');

unh? There are three files with file_version = 2

CREATE TABLE Cells
(cell_id INTEGER NOT NULL PRIMARY KEY,
cell_name VARCHAR(150)NOT NULL,
file_id INTEGER NOT NULL
REFERENCES Files(file_id),
file_type CHAR(2) NOT NULL,
sector_id INTEGER NOT NULL) ;

/*What is a cell and why does it have file data in it?
--- If books and authors have an authorship relationship; a book is not an attribute of an author */

INSERT INTO Cells
VALUES (1, 'Alex', 11, 'D1', 111),
(2, 'Barbara', 22, 'D1', 112),
(3, 'Chris', 33, 'D1', 113),
(4, 'Don', 44, 'D1', 111),
(5, 'Jane', 55, 'D1', 112),
(6, 'Ronny', 66, 'D1', 113);

why are candidate names in Cells and not in Candidates?

This is about as far as I can get. Maybe the w2indow functions will help

SELECT C.candidate_id, F.file_id,
MAX(F.file_version) OVER (PARTITION BY F.file_id)
FROM Candidates AS C, Files AS F
WHERE C.candidate_id = 2
AND C.candidate_id = F.candidate_id ;

This whole thing does not make sense to me.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Megha16
Starting Member

4 Posts

Posted - 2011-05-30 : 09:42:57

Hello Arvindam, your query return me maximum file version from whole table but I want to group each record by sector Id along with max version for particular record. not max file version from whole table. Thanks

Hi jcelko , here is answer to your question :

1)Primary is not an adjective here , primary is one of my field which has two values 1 and 0 , I want max of all records where primary is 1 only.

2) (file_id, file_version) is not related to each other , they are two totally different fields, file_version is only one column in table .

3)Yes, there are 3 files with file_version 2 , as i mentioned each record is associated with sector id and there are three sectors per record so basically each record has three rows. and I want max of file_version for each record along with sector id for priamry(field name) 1.

4) tbl_cells is child table of tbl_files and it has foreign key file_id which is being taken from tbl_files and yes it has only file_id from tbl_cells , rest of the data is its own.

5) Candidates name is not into cell table , tbl_candidates has only candidate_id(foreign kep for tbl_files) and candidate_name , and primary value(1,0)

Any help is appreciated , I tried putting this question on MYSQL forum but no luck yet.

Thanks guys !!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources