R8VI
asked on
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
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
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.
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.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
EXISTS (SELECT 0 FROM YourTable F2 WHERE F1.Fname < F2.Fname AND F1.Filename <> F2.Filename AND F1.MSDN = F2.MSDN)
ASKER
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
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
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)
EXISTS (SELECT 0 FROM YourTable F2 WHERE F1.Fname <> F2.Fname AND F1.Filename <> F2.Filename AND F1.MSDN = F2.MSDN)
ASKER
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
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
I am quite positive that my query should do what you are requesting ...
but indeed, I did not test it ...
but indeed, I did not test it ...
ASKER
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
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
Why don't you just make an ORDER BY MSDN?
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)
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)
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
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
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
ASKER
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
Thanks for this one last thing is that I need to also show the MDSN from the second file as well
Thanks,
R8VI
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
)
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
)
ASKER
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
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
ASKER
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
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
>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?!!
well, if the "join" condition is on MSDN, it will be the same value for both records, no?!!
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
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
ASKER
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
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
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?
ASKER
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
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
Open in new window
or get all the rows for that msdn:
Open in new window