?
Solved

SQL query on self join

Posted on 2011-10-07
23
Medium Priority
?
309 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:R8VI
  • 9
  • 5
  • 4
  • +2
23 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36929973
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
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 36929978
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
 

Author Comment

by:R8VI
ID: 36930064
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36930155
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
 
LVL 18

Expert Comment

by:deighton
ID: 36930204
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
 

Author Comment

by:R8VI
ID: 36930216
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
 
LVL 18

Expert Comment

by:deighton
ID: 36930265
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
 

Author Comment

by:R8VI
ID: 36930277
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36930349
I am quite positive that my query should do what you are requesting ...
but indeed, I did not test it ...
0
 

Author Comment

by:R8VI
ID: 36930380
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
 
LVL 4

Expert Comment

by:MarioAlcaide
ID: 36930400
Why don't you just make an ORDER BY MSDN?
0
 
LVL 18

Expert Comment

by:deighton
ID: 36930409
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
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36930549
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36930619
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
 

Author Comment

by:R8VI
ID: 36931531
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
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36931883
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
 

Author Comment

by:R8VI
ID: 36932294
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
 

Author Comment

by:R8VI
ID: 36932437
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36933448
>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
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36935543
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
 

Author Comment

by:R8VI
ID: 36938053
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
 
LVL 5

Expert Comment

by:DavidMorrison
ID: 36938317
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
 

Author Comment

by:R8VI
ID: 36938357
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question