SQL Wizardry Required!

[ColinM]

Hi folks,
Bit of a tricky one here, well for me anyway.
As an example I'll use the system tables.

Query 1
-------

select t.xtype, c.name from systypes t LEFT OUTER JOIN syscolumns c ON t.xtype = c.xtype AND c.name = 'info'
ORDER BY t.xtype

This query outputs all the types and the name beside it (if it exists). Basically all I need to do is re-write the query so that the c.name = 'info' isn't in the JOIN clause (as DTS doesn't appear to like parameters in the JOIN clause). I assumed that I could simply move it to the WHERE clause but that doesn't give you the same results! (see Query 2) Can ANYBODY solve this??

Query 2
-------

select t.xtype, c.name from systypes t LEFT OUTER JOIN syscolumns c ON t.xtype = c.xtype
WHERE c.name = 'info'
ORDER BY t.xtype

 

[bperry]

I'd be willing to believe that the two queries do not return the same result set. Basically, the restriction on the right-hand table is over-riding the Left Join and eliminating some rows. i.e. The WHERE clause acts to exclude records from the result set, even though we used a Left Join. This is behavior you might not expect to see, and lots of people have questions about it.

I assume by using the LEFT JOIN that you want to preserve all rows from the left-hand table (t). To get the result you want, I believe you need something like this:

select t.xtype, c.name
from systypes t
LEFT OUTER JOIN syscolumns c
ON t.xtype = c.xtype
AND (c.name = 'info' OR c.name IS NULL)
ORDER BY t.xtype

However, you can try this one below if you like, although I am thinking it might not give you the result set you need.

select t.xtype, c.name
from systypes t
LEFT OUTER JOIN syscolumns c
ON t.xtype = c.xtype
WHERE (c.name = 'info' OR c.name IS NULL)
ORDER BY t.xtype
--------------------------------------
That's a long explanation of the situation, but I'm not sure how to get either one to run properly from DTS. Hopefully another poster will add a suggestion, or maybe the DTS site might have some info.

http://www.sqldts.com

If you're interested, here's a fuller explanation of the left-join problem, but it isn't written all that well.

http://www3.ns.sympatico.ca/brianperry/sqlnotes/joins4.html

 

[ColinM]

Thanks BPerry for the reply.
Yes, the WHERE clause restricts the rows.
Unfortunately the second query only works on NULLS and has not the desired effect.
Looking at the explanation from the Microsoft techie it may not be possible?

 

[bperry]

Well, I think my first suggested query does what you want, right? (I am thinking that it should.) Still assuming, of course, that when you used the LeftJoin your goal was to preserve rows from systypes t

Select t.xtype, c.name
from systypes t
LEFT OUTER JOIN syscolumns c
ON t.xtype = c.xtype
AND (c.name = 'info' OR c.name IS NULL)
ORDER BY t.xtype

But now your headache is getting that into a DTS package??

 

[tlbroadbent]

I agree that the query suggested by bperry will work. Here is an alternative syntax that I prefer, using a sub-query to select rows based on the criterion. In SQL 2000, the query execution plan is the same for ColinM's original query, bperry's query and the following. So take your pick.

Select t.xtype, c.name
From systypes t
LEFT OUTER JOIN
(Select name, xtype
From syscolumns
Where name = 'info') c
On t.xtype = c.xtype
Order By t.xtype Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[bperry]

I like that, too. Could maybe see myself sqinging both ways. Sometimes use one, sometimes use the other.

The syntax for the derived table approach seems a bit shorter, that's a good thing.

The syntax for my suggestion exposes what the problem is (perhaps needlessly so), for someone who wants to understand why such an approach is required.

Good suggestion.

 

[ColinM]

Thanks for the help guys!
I thought that TBroadbent's query would work, but it still won't parse a join with a parameter in DTS. (The 'info' is replaced with a ?).
However it did get my brain thinking and the solution is fairly simple. Just take the query and put it in a stored procedure that takes the variable.

CREATE PROCEDURE spGetTypes @Name varchar(50) AS
Select t.xtype, c.name
From systypes t
LEFT OUTER JOIN
(Select name, xtype
From syscolumns
Where name = @Name) c
On t.xtype = c.xtype
Order By t.xtype

And then in DTS I can simply use
EXEC spGetType ?

Whew, that was a struggle!