SQL query on self join

Hi,

I have a table that has colums

MSDN, Fname, Filename
111       pat         test.csv
112       pat2       test.csv
113       pat3       test.csv
111       patel       test1.csv
112       pat2       test1.csv
113       pat3       test1.csv


so what the above query should produce is show 2 records
which is the first record and 4th record as they are the same MSDN in 2 diffrent file names but the fname is different


Please help

Thanks,

R8VI

R8VIAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
ok, let's see:
select sq.msdn
, max(case when rn = 1 then fname end) fname_1
, max(case when rn = 1 then filenameend) filename_1
, max(case when rn = 2 then fname end) fname_2
, max(case when rn = 2 then filenameend) filename_2
, max(rn) max_rn
from (
select t.*
, row_number() over (partition by msdn order by filename) rn 
from yourtable t
where t.msdn in ( 
      select msdn
        from yourtable
      group by msdn
      having min(fname) <> max(fname)
 )
) sq
group by msdn

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could start with:

select msdn, min(fname), max(fname) from yourtable
group by msdn
having min(fname) <> max(fname)

Open in new window


or get all the rows for that msdn:
select t.* 
from yourtable t
where t.msdn in ( 
      select msdn
        from yourtable
      group by msdn
      having min(fname) <> max(fname)
 )

Open in new window

0
 
MarioAlcaideCommented:
Hi,

I'm not sure what exactly are you trying to do, if you just want to show the MSDN 111, then you can add

where MSDN = 111.

Please clarify.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
R8VIAuthor Commented:
Hi Elite,

I could not to the where not in because that will only show me the records for one file

I need to know a side by side comparison for both files and what is different so my results could be something like this

MSIDN first file | Fname first file | filename first file | MSISDN second file | Fname second file| filename second file

and list the results from first and second file

please help

Thanks,

R8VI
0
 
deightonprogCommented:
SELECT F1.* FROM YourTable F1 WHERE
     EXISTS (SELECT 0 FROM YourTable F2 WHERE F1.Fname < F2.Fname AND F1.Filename <> F2.Filename AND F1.MSDN = F2.MSDN)
0
 
R8VIAuthor Commented:
Hi,


I am sorry but could you explain your query I am not sure it works

as what I need is to bring  all data that is in both filenames where any of the records are different and MSDN is the same in both filename

Please help

Thanks,

R8VI
0
 
deightonprogCommented:
SELECT F1.* FROM YourTable F1 WHERE
     EXISTS (SELECT 0 FROM YourTable F2 WHERE F1.Fname <> F2.Fname AND F1.Filename <> F2.Filename AND F1.MSDN = F2.MSDN)
0
 
R8VIAuthor Commented:
Hi Angellll,

I tried your query and it does the opposite of what I want to do , because It returns everything where the first name is equal I need it to be not equal where the MSDN's are equal from both files


Hi Deighton,

I tried your query as well but i cant get the records to display from the second filename so I cant tell if its right

Please help

Thanks,

R8VI
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am quite positive that my query should do what you are requesting ...
but indeed, I did not test it ...
0
 
R8VIAuthor Commented:
Hi,

ok I should be a bit more clear

I have many coloums I have just shown a few

what I need to do is

where in both filename MSDN are equal
if fname is not equal for a particular MSDN bring the record back
if sname is not equal for a particular MSDN bring the record back

and so on

please help

Thanks,

R8VI
0
 
MarioAlcaideCommented:
Why don't you just make an ORDER BY MSDN?
0
 
deightonprogCommented:
try my second query, that gives



111             pat             test.csv                                
111             patel           test1.csv                              


here

SELECT F1.* FROM YourTable F1 WHERE
     EXISTS (SELECT 0 FROM YourTable F2 WHERE F1.Fname <> F2.Fname AND F1.Filename <> F2.Filename AND F1.MSDN = F2.MSDN)
0
 
DavidMorrisonCommented:
I'm guessing but it sounds like need to do something like:


select T1.*
from YourTable as T1
join YourTable as T2
         On T1.MSDN = T2.MSDN
         and T1.filename = T2.FileName
         and (
                  T1.Fname <> T2.FName
                  or
                  T1.SName <> T2.SName
                 )


Thanks

Dave
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, so you have to add all those conditions and fields to the SQL as needed:
select sq.msdn
, max(case when rn = 1 then fname end) fname_1
, max(case when rn = 1 then filename end) filename_1
, max(case when rn = 1 then colA end) colA_1
, max(case when rn = 2 then fname end) fname_2
, max(case when rn = 2 then filename end) filename_2
, max(case when rn = 2 then colA end) colA_2
, max(rn) max_rn
from (
select t.*
, row_number() over (partition by msdn order by filename) rn 
from yourtable t
where t.msdn in ( 
      select msdn
        from yourtable
      group by msdn
      having min(fname) <> max(fname)
          or min(COLA) <> max(COLA)
 )
) sq
group by msdn

Open in new window

0
 
R8VIAuthor Commented:
HI Angelll,

Thanks for this one last thing is that I need to also show the MDSN from the second file as well

Thanks,

R8VI
0
 
DavidMorrisonCommented:
Unless I'm missing the point do you actually need to make sql server jump through those hoops Angelll has coded?

wont the way I have proposed work, just using field name aliases?

select T1.MDSN as MDSN_1, T1.Fname as FName_1, T1.Sname as Sname_1, T1.Filename as Filename_1, T2.MDSN as MDSN_2, T2.Fname as Fname_2, T2.Sname as Sname_2, T2.Filename as Filename_2
from YourTable as T1
join YourTable as T2
         On T1.MSDN = T2.MSDN
         and T1.filename = T2.FileName
         and (
                  T1.Fname <> T2.FName
                  or
                  T1.SName <> T2.SName
                 )
0
 
R8VIAuthor Commented:
Hi David,

I am sorry I just didnt know how to do it so whichever came first to be honest I can try your way as well I have no problem.

How do I get the SQL to show an additional coloum of what is different as well

Please help thanks,

R8VI

Thanks
0
 
R8VIAuthor Commented:
HI,
One other thing that needs to be considered is the file name I need only where file name is test.csv and test1.csv not any other filename as there are many filenames in the DB

please help

Thanks,

R8VI
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>that I need to also show the MDSN from the second file as well
well, if the "join" condition is on MSDN, it will be the same value for both records, no?!!
0
 
DavidMorrisonCommented:
Hi R8VI,

well the best thing to do is try both suggested queries and see which preforms better for you.

If you want to show additional columns add them to the list of columns after the 'select' statemet, ensureing you add the 'as  <fieldname>_x as per the pattern for the existing columns.

If you wish to return rows where another field value differs then simply add it to the bottom part of the 'where' clause as below:

 and (
                  T1.Fname <> T2.FName
                  or
                  T1.SName <> T2.SName
                  or
                  T1.<Field Name> <> T2.<Field name>
                  or
                  T1.<Field Name> <> T2.<Field name>  
                 )

and so on.

In order to filter for a specific file name just change the part of the 'where' clause that says "and T1.filename = T2.FileName" to "and T1.Filename = 'Test.csv' and T2.FileName = 'Test.csv' "


Hope this helps

Thanks

Dave
0
 
R8VIAuthor Commented:
Hi Dave / angelll,

Thanks for this I'll try both and see which gives best results

One other thing I need to show is the difference which columns is different from first file to second file

Either by saying it's different in the row or adding another row and saying the colum name which is different

Please help

Thanks,

R8vi
0
 
DavidMorrisonCommented:
R8VI, my version will print both both records on the same row for you so you can see what is different, is that not what you want?
0
 
R8VIAuthor Commented:
HI,

Yep thats fine no problem that  it will display next to each other but I have 20 columns so in total there are about 40 columns so its become a bit difficult to see what are the differences

So if something like if its the same then in the results do nothing and bring back the results else if its different write different in the column so in my example above the query should return something like

firstMSDN  firstfname  firstfilename     secondMSDN     seondfname  secondfilename
111             different             test.csv             111                     different            test1.csv


Thanks for all your help so far

Thanks,

R8VI
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.