October 5, 2007 at 8:07 am
I am trying to create a query that joins two tables. The first table, 'TestResults', contains exaclty that and the second table, 'ReportsLayOut' contains part of a layout for reports.
TestResults:
productCode varchar(15)
testelement varchar(10)
testvalue varchar(10)
ReportsLayout
testelement varchar(10)
displayorder int
The ReportsLayout table has 25 entries for different test that are ran on our product and the order in which the are displayed on the report.
Depending on the product it could have all 25 test ran and have results in the TestResults table or it could have 2 or 3.
What I need to do is even if the product has only 3 test I need to show all 25 test, ie if no test it will say 'N/A'.
Here is the statement I am using:
Select
rl.TestElement,
tr.TestVlaue,
tr.ProductCode
From
ReportLayout AS rl
Left Outer Join
TestRetults AS tr
On
rl.testelement = tr.testelement
Where
tr.productCode = @productCode
The results are fine when the productCode has all 25 test run, but whet it has less I only get the elements that are in tr.
October 5, 2007 at 8:16 am
You seem to have run out of steam a bit there, Aaron! When you get round to posting the rest of your select statement, please will you also provide some sample data. It's helpful if your table definitions are in the form of CREATE TABLE statements and your sample data is in the form of INSERT statements - that makes it easy for us to test what we come up with.
Thanks
John
October 5, 2007 at 8:25 am
Aaron,
Your problem is your WHERE clause.
If you're using an OUTER JOIN and then you use a WHERE clause to define something in the "inner" table (in this case your TestResults table), the WHERE clause will always override the OUTER JOIN and essentially force an INNER JOIN on your results.
The best way to get around it is to take your WHERE clause syntax and add it into the ON line.
Example:
Select
rl.TestElement,
tr.TestVlaue,
tr.ProductCode
From
ReportLayout AS rl
Left Outer Join
TestResults AS tr
On rl.testelement = tr.testelement
and tr.productCode = @productCode
--Where
I've commented out the Where because it's no longer necessary. Putting the conditional in the ON line will limit the records coming from TestResults without limiting the records in ReportLayout.
*However* (there's always a caveat. @=) using a conditional in your ON line can cause processing issues if used carelessly. So only do it in cases such as this one, where you really need the OUTER JOIN and the conditional. Don't do it in cases where you're using INNER JOIN or the conditional is applied to the OUTER table.
Make sense?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply