Searching two tables for one result

[UnsolvedCoding]

Ok, while working on the solution to the last post I came across another one that I know should be simple but I can't find the answer.

Here are the two queries.

Select R.Report_Number
, S.System_Report_Number
From Report as R
Inner Join Report_Table as S
On Cast(Cast(R.Report_Number as Int) as Varchar (20) = s.System_Report_Number



Select R.Report_Number
, S.System_Report_Number_Replacment
From Report as R
Inner Join Archived_Report_Table as S
On Cast(Cast(R.Report_Number as Int) as Varchar (20) = s.System_Report_Number_Replacment


I am taking the info from the report, scrubbing it against the first table and if here is no system report then scrubbing it against the archives.

How can I make this one query?

 

[Qik3Coder]

level set:
sometimes a report is removed from "current reports" and placed in Archived reports.

Report is an instance of a specific report type. (which may have archived)

Select R.Report_Number, Coalesce(S.System_Report_Number, s_a.System_Report_Number_Replacment) ReportNumber
From Report as R
LEFT Join Report_Table as S On
        Cast(Cast(R.Report_Number as Int) as Varchar (20) = s.System_Report_Number
LEFT Join Archived_Report_Table as S_A
        On Cast(Cast(R.Report_Number as Int) as Varchar (20) = s_a.System_Report_Number_Replacment

Something like that
Lodlaiden


I haz all the letters: SME, BA, QA, PM, DEV, DBA, UAT, SE, HD

 

[Rhys666]

I would suggest doing some research on 'INTERSECT' and 'EXCEPT'. Very, very useful for set based comparisons.

Can I also asks why there is so much CASTing going on? I would immediately pick this up as an indication of a probble design flaw.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[UnsolvedCoding]

Regarding the casting - The report and the system hold information in different formats. One in Excel the other in SQL, so they have to be cast the same.

And due to the size of the database it runs much faster to declare a cast instead of let the second cast be implicit.

 

[Olaf Doschke]

First I think you're missing a closing ) and second I'd think about unioning these two results.

Especially if the joined info can only be in the Report_Table or in the Archived_Report_Table, one of the results will always be empty, the other will have the wanted result, so unioning them will always have the result you want, you just need to rethink the different column names, eg you can't union System_Report_Number in the upper query with System_Report_Number_Replacment. If you want to indicate the one result comes from the archive, add another bit column, eg:

Select R.Report_Number
    , S.System_Report_Number
    , Cast(1 As bit) As IsLiveData 
From Report as R
    Inner Join Report_Table as S
        On Cast(Cast(R.Report_Number as Int) as Varchar (20) = s.System_Report_Number
Union
(
Select R.Report_Number
    , S.System_Report_Number_Replacment as System_Report_Number
    , Cast(0 As bit) As IsLiveData 
From Report as R
    Inner Join Archived_Report_Table as S
        On Cast(Cast(R.Report_Number as Int) as Varchar (20) = s.System_Report_Number_Replacment
)

IsLiveData = 1 means it's coming from Report_Table, IsLiveData=0 means, it comes from the archive table and the System_Report_Number is a replacement number.

Bye, Olaf.

 

[Rhys666]

Having to cast to an int then to a varchar in orde to get an efficient join says bad design to me. The referential columns sould be of the same type meaning that no casting at all would be required.

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett