Getting Data from two tables

Question

I have two tables one of USERS and second of UserFiles just like below UserTable UserId Name Email Phone 1 New New@new.co 0909000 2 Old old@old.com 0998099 UserFiles FileID(Primary) UserID(Foreignkey) FileName DateCreated Active 1 1 MyResume.Doc 2/2/2016 true 2 1 Image.JPG 2/17/2016 true 3 1 Cover.Docx 2/20/2016 true 4 2 Card.png 1/1/2017 true Now I have a gridview I want to show one File for each user I mean the latest file for this user According to datecreated for example here I have three files for userid=1 but i just want to show the Cover.Docx because it is latest one for this user according to datecreated Field. I want this record how to do that RecordID UserId FileName Email Phone 1 1 Cover.Docx New@new.co 0909000 2 2 card.png old@old.com 0998099 I want that record how to achived that

theLizard · Answer

select user.[id], userfiles.[filename], userfiles.[datecreated], userfiles.active FROM user INNER JOIN userfiles ON user.[id] = userfiles.[id1] WHERE userfiles.[datecreated] >= (SELECT datecreated from userfiles order by [id1], datecreated DESC)

Mark Tabor · Answer

Dear Pankaj your last query SELECT DI.DynamicIndicatorID,Di.District,tpl.filenameName , Di.Quarter,DI.District,DI.TFA,DI.TFB,DI.TFG,DI.TFW,DI.TFM from DynamicIndicatorsTargetsForPu AS DI Left Outer Join tblUploadedFiledetailsForPartners as tpl ON tpl.IndicatorID=DI.DynamicIndicatorID WHERE tpl.fileID =(SELECT max(tpl.fileID) FROM tblUploadedFiledetailsForPartners tpl WHERE tpl.IndicatorID=DI.DynamicIndicatorID AND DI.UserId=61 GROUP BY tpl.IndicatorID) gives correct result for files but it is not showing the other records i,e if i have four rows in first table and 3 rows in second table {2 for userid=1, and one for userid=2} then it is showing the latest file for userid=1 and the right file for userid=2 , but it is not showing the other two records which do not have any file in table first.why it is so? i need all four records and show nothing in the filename column if it do not have any file

Mark Tabor · Answer

Dear Pankaj your query gives that error Msg 147, Level 15, State 1, Line 6 An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

ali tuncer · Answer

try this : select ufs.FileId,uf.userid,ufs.Filename,u.Email,u.Phone from dbo.UserTable u inner join ( select UserId ,Max(DateCreated) as CreateDate from dbo.Userfiles group by UserId) uf on u.UserId = uf.UserId inner join dbo.Userfiles ufs on uf.userid = ufs.UserID and uf.CreateDate = ufs.DateCreated

Mark Tabor · Answer

if my first table i mean my user table have more than two rows then it is just returning two rows i want all rows frm first table and i want to show the last inserted file for each user one file

Nitin Sontakke · Answer

Hello, Here is another solution, which I haven't tested. SELECT ut.UserId, uf.FileName, ut.Email,ut.Phone FROM UserTable ut left outer join ( select UserId, max(DateCreated) DateCreated from UserFiles where Active = 'true' group by UserId ) ufx left outer join UserFiles uf ON uf.UserId = ufx.UserId and uf.DateCreated = ufx.DateCreated This avoids co-related sub-query.

Vasanth Natarajan · Answer

try this.... select ut.UserId, uf.FileName, ut.Email,ut.Phone,max(uf.DateCreated)as created_date FROM UserTable ut,UserFiles uf where ut.UserId=uf.UserID Group by ut.UserID;

Pankaj Kumar Choudhary · Answer

Hello! Mark try this query SELECT ut.UserId, uf.FileName, ut.Email,ut.Phone FROM UserTable ut LEFT OUTER JOIN UserFiles uf ON ut.UserId=uf.UserID WHERE uf.FileID=(SELECT max(uf2.FileID) FROM UserFiles uf2 WHERE uf2.UserID=uf.UserID GROUP BY uf2.UserID)

UserId	Name	Email	Phone
1	New	[email protected]	0909000
2	Old	[email protected]	0998099

FileID(Primary)	UserID(Foreignkey)	FileName	DateCreated	Active
1	1	MyResume.Doc	2/2/2016	true
2	1	Image.JPG	2/17/2016	true
3	1	Cover.Docx	2/20/2016	true
4	2	Card.png	1/1/2017	true

RecordID	UserId	FileName	Email	Phone
1	1	Cover.Docx	[email protected]	0909000
2	2	card.png	[email protected]	0998099