October 3, 2007 at 11:32 am
What am I missing? The following query does not return results from the Webs table (left join) even though the data exist. Remove the 'd.ListID is not NULL' and everything works find by I need it to filter the data.
Select w.* from webs w LEFT JOIN docs d ON w.id = d.webid
WHERE w.id = '12345'
AND d.TYPE <> 1
AND d.ListID IS NOT NULL
Thanks
October 3, 2007 at 11:43 am
That is because this query will be processed by the query optimizer as an INNER JOIN instead of a LEFT JOIN because your WHERE clause is telling SQL Server that your 'd' table values. Can you give an example with some sample data on exactly what you want?
October 3, 2007 at 11:51 am
Correct me if I'm wrong, but it looks like you are trying to use the LEFT JOIN to eliminate rows. If there is a match in the docs table, you only want webs rows where the doc.type is not equal to 1 and the listID is not null. If this is correct, then try this query, it may be what you are looking for. Keep in mind that I did not test this, that is for you:)
SELECT w.*
FROM webs.w
LEFT JOIN (
SELECT id
FROM docs d
where d.TYPE = 1 OR d.ListID IS NULL
) d
ON w.id = d.id
WHERE w.id = '12345'
AND d.id IS NULL
October 3, 2007 at 12:14 pm
Any use of the outer table in the WHERE clause will change your outer join to an inner join. You can use the subquery method shown above, or move the filter conditions to the ON clause.
Select w.*
from webs w
LEFT JOIN docs d
ON w.id = d.webid
AND d.TYPE <> 1
AND d.ListID IS NOT NULL
WHERE w.id = '12345'
October 3, 2007 at 12:41 pm
:w00t:, after 3 times you'd think that it would be enough. So I won't tell you again.
I will however suggest that you can have this functionnality by moving the where clause in the left join clause (left join ... on id=di AND something='whatever' where Something is null
Play with sample data in a small table and you'll see how to make this work.
October 3, 2007 at 2:36 pm
Thanks everyone for your input. I did try originally having the IS NOT NULL condition part of the join clause using an INNER and LEFT join but the result were the same, no records. The "IS NOT NULL" condition eliminates the only matching row from the docs (d) table but I still need the info from the webs (w) table wheather there is a matching record in the docs table or not.
October 3, 2007 at 2:39 pm
So do you still need help, or you're fine?
October 3, 2007 at 2:47 pm
You just need to change your query to the following:
Select w.* from webs w
LEFT JOIN docs d
ON w.id = d.webid
and w.id = '12345'
AND d.TYPE <> 1
AND d.ListID IS NOT NULL
October 3, 2007 at 2:47 pm
You just need to change your query to the following:
Select w.* from webs w
LEFT JOIN docs d
ON w.id = d.webid
and w.id = '12345'
AND d.TYPE <> 1
where d.ListID IS NOT NULL
October 3, 2007 at 2:56 pm
That's still an INNER JOIN Felix.
Marcus,
Either try Scott C.'s or Remi's suggestion or post your sample data and expected result so the rest of us have something to workwith instead of guessing what you're after.:)
October 3, 2007 at 3:02 pm
I must be blind now... what's wrong with the last suggested query?
October 3, 2007 at 3:11 pm
Maybe it's me who's blind, or maybe just confused becasue the OP hasn't defined what he wants real well. I was under the impression from the first post in the thread that the OP wants all rows from the webs table regardless of if a match exists in docs, hence his LEFT JOIN. The last query still will result in an INNER JOIN because he is referencing values from the docs table in the WHERE clause. Only rows that fit his criteria of d.ListID IS NOT NULL will be returned which tells me that a row must exist in the docs table to satisfy the query. If a row must exist in the docs table, this is an INNER JOIN and not what the OP wants. Am I missing something (this has been a long day)?
October 3, 2007 at 4:24 pm
I see all the filters in the inner join (except for the is null)...
Select w.* from webs w
LEFT JOIN docs d
ON w.id = d.webid
and w.id = '12345'
AND d.TYPE <> 1
where d.ListID IS NOT NULL
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply