How to get distinct records from two tables in SQL

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

I have two tables
First table(DynamicIndicatorID(P),Question_ID,Quarter,Year,District)
P stands for primary key and F stands for foreign key
second table(fileID,FileName,DynamicIndicatorID(F),Datecreated)

Now In my first table I have four rows just like that

(1 , 1, 1, 1 , 1)
(2 , 1, 1, 2 , 1)
(3 , 1, 2, 1 , 1)
(4 , 1, 1, 1 , 2)
and in my second table I have three rows , my second table basically containing the files for each DynamicIndicatorID , it can contain Zero OR Many rows for each DynamicIndicatorID

just like below

second Table
(1,First.Docx,1,3/3/2001),
(2,New.Docx,1,3/3/2016),
(3,second.Docx,2,3/3/2001),

Now I want to write a query which can give me all records from first table {i.e. four Rows}
I want to add the FileName column as well so I need this record
(DynamicIndicatorID,Question_ID,Quarter,Year,District,FileName)
(1,1,1,1,1,New.Docx),
(2,1,1,2,1,Second.Docx),
(3,1,1,1,1,Nothing),
(4,1,1,1,1,Nothing),
I need to show all rows from first table and I want to show the latest file for each indicator just like above i have two files for DynamicIndicatorID=1 i am getting the latest one New.Docx , and for DynamicIndicatorID=2 I just have one so i am showing that and for DynamicIndicatorID 2,3 I have not any file so I have to show nothing , The mean idea is that I want to get all rows from First table and I want to show the latest file for each dynamicindicatorID , every DynamicIndicatorID must have at most one file which should be the latest one .

What I have tried:

i have tried that , it is giving the good result the latest file for each indicator but it is not showing the other two records which dont have any file
below is my query
SELECT Distinct 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(fileID) from tblUploadedFiledetailsForPartners as tpl where tpl.IndicatorID=DI.DynamicIndicatorID) AND DI.UserId=61

Posted 13-Jun-16 18:36pm

Malikdanish

Updated 14-Jun-16 20:09pm

Add a Solution

Comments

ZurdoDev 14-Jun-16 8:28am

I don't quite follow, but it sounds like all you have to do is select * from first table and join to a derived table of essentially (SELECT fileid, max(id) from table group by fileid) so that you only get one file per table 1 entry.

Malikdanish 14-Jun-16 11:48am

it is not showing the records who does not have file in second table

ZurdoDev 14-Jun-16 11:49am

I have no idea what you have done, but my guess is you did an INNER JOIN instead of a LEFT JOIN.

Malikdanish 14-Jun-16 11:51am

I did Left Outer Join
select DI.DynamicIndicatorID,Di.District ,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.fileID IN ( select max(fileID) from tblUploadedFiledetailsForPartners group by fileID)
where DI.Userid=61 AND DI.DynamicIndicatorID=tpl.IndicatorID

Malikdanish 14-Jun-16 11:52am

DynamicIndicatorID primary key
AND IndicatorID Foreign key

ZurdoDev 14-Jun-16 12:00pm

Not quite.
SELECT t1.*, x.fileid
FROM table1 t1
LEFT JOIN (
SELECT id, MAX(fileid) AS fileid
FROM table2
) x ON t1.id = x.id

Something like that.

Malikdanish 15-Jun-16 0:30am

No worked , thanks for putting the time i am trying

Malikdanish 15-Jun-16 2:03am

SELECT t1.DynamicIndicatorID,t1.District,t1.Quarter,t1.District,t1.TFA,t1.TFB,t1.TFG,t1.TFW,t1.TFM,x.fileID as fileID,x.filenameName as FileName
FROM DynamicIndicatorsTargetsForPu t1
LEFT JOIN (
SELECT IndicatorID, MAX(fileID) AS fileid,filenameName
FROM tblUploadedFiledetailsForPartners group by IndicatorID
) x ON t1.DynamicIndicatorID = x.IndicatorID where t1.UserId=59

it is saying "Msg 8120, Level 16, State 1, Line 4
Column 'tblUploadedFiledetailsForPartners.filenameName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." otherwise it works

ZurdoDev 15-Jun-16 7:18am

If you want to pull an additional field out of the 2nd table you can easily do another join to that table to get all that you want. All that should be in the derived table is the id to join on so that you get a handle on just the one record.

so...
) x ON t1.DynamicIndicatorID = x.IndicatorID
-- you can join to the 2nd table to get anything out of it you want
INNER JOIN table2 t2 ON x.IndicatorID = t2.IndicatorID
where t1.UserId=59

Then remove the filenameName from the derived table.

Malikdanish 16-Jun-16 0:05am

how to add filename from table2 with the below query

SELECT t1.DynamicIndicatorID,t1.District,t1.Quarter,t1.District,t1.TFA,t1.TFB,t1.TFG,t1.TFW,t1.TFM,x.fileID as fileID
FROM DynamicIndicatorsTargetsForPu t1

LEFT JOIN (
SELECT IndicatorID, MAX(fileID) AS fileid
FROM tblUploadedFiledetailsForPartners group by IndicatorID
) x ON t1.DynamicIndicatorID = x.IndicatorID where t1.UserId=61

ZurdoDev 16-Jun-16 7:15am

I already explained it in my previous response. You can join to the table again.

Malikdanish 17-Jun-16 0:42am

SELECT t1.DynamicIndicatorID,t1.District,t1.Quarter,t1.District,t1.TFA,t1.TFB,t1.TFG,t1.TFW,t1.TFM,x.fileID as fileID ,TP.filenameName
FROM DynamicIndicatorsTargetsForPu t1
INNER JOIN tblUploadedFileDetailsForPartners as TP on TP.IndicatorID=t1.DynamicIndicatorID
LEFT JOIN (
SELECT IndicatorID, MAX(fileID) AS fileid
FROM tblUploadedFiledetailsForPartners group by IndicatorID
) x ON t1.DynamicIndicatorID = x.IndicatorID

where t1.UserId=59
it is giving all records instead of the latest Max(fileID)

ZurdoDev 17-Jun-16 7:15am

That is because you did the join in the wrong order. Look, I already gave you the answer, if you aren't going to follow it then I don't know how to help you.

Malikdanish 17-Jun-16 1:15am

SELECT t1.DynamicIndicatorID,t1.District,x.filenameName,t1.Quarter,t1.District,t1.TFA,t1.TFB,t1.TFG,t1.TFW,t1.TFM,x.fileID as fileID ,x.filenameName,x.IndicatorID
FROM DynamicIndicatorsTargetsForPu t1
LEFT JOIN (
SELECT tb.IndicatorID,tb.filenameName,tb.filePath, tb.Createdby,tb.fileID
FROM tblUploadedFiledetailsForPartners as tb
INNER JOIN
(SELECT IndicatorID, MAX(fileID) AS fileid
FROM tblUploadedFiledetailsForPartners group by IndicatorID)Tab
ON
tab.fileid=tb.fileID AND
tab.IndicatorID=tb.IndicatorID
) x ON t1.DynamicIndicatorID = x.IndicatorID

where t1.UserId=59
that is good one

Malikdanish 14-Jun-16 11:27am

I have tried this but it does not work ? can you write it how you are saying ?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 11139743 · Accepted Answer · 2016-06-14T20:09:00

Solution 1

Try executing this:

SELECT DI.DynamicIndicatorID,DI.Question_ID,DI.Quarter,DI.Year,DI.District,tpl.FileName from DynamicIndicatorsTargetsForPu
AS DI
Left Outer Join tblUploadedFiledetailsForPartners as tpl on tpl.DynamicIndicatorID=DI.DynamicIndicatorID
AND tpl.FileId in (select max(tb1.FileId) from tblUploadedFiledetailsForPartners as tb1 where tb1.DynamicIndicatorId = DI.DynamicIndicatorId)

Hope this helps!!

Posted 14-Jun-16 20:09pm

Member 11139743

Comments

Malikdanish 15-Jun-16 5:36am

it works some how ? i am trying to get full result from that and then will mark you the right ghy :)

Malikdanish 17-Jun-16 2:07am

it works