Getting Data from two tables

Mark Tabor

10y1.3k

I have two tables one of USERS and second of UserFiles just like below

UserTable

UserId	Name	Email	Phone
1	New	[email protected]	0909000
2	Old	[email protected]	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	[email protected]	0909000
2	2	card.png	[email protected]	0998099

I want that record how to achived that

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

theLizard10y ago
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 Tabor10y ago
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 Tabor10y ago
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 tuncer10y ago
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 Tabor10y ago
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 Sontakke10y ago
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 Natarajan10y ago
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 Choudhary10y ago
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)