Left Join and WHERE

Chris_Evans

Hi there,
My SQL query now stands as follows:

SELECT links.id, links.title, relatedlinks.id, relatedlinks.link_id, relatedlinks.shop_id
FROM links
LEFT JOIN relatedlinks 
ON links.id = relatedlinks.link_id 
WHERE relatedlinks.shop_id = '2' OR relatedlinks.shop_id IS NULL AND links.id != '5'
ORDER BY links.title ASC  LIMIT 0 , 30

When relatedlinks contains a row that has shop_id='2' it shows the relevant rows in the links table and when it can't find any relatedlinks rows that relate to the links it works fine too.

However, when there are links in the relatedlinks table and the shop_id is not equalling to '2' - it doesn't show the relevant links.

I believe the problematic part of this statement is the relatedlinks.shop_id IS NULL... - it needs to be something else - without it - it works perfectly BUT it shows duplicate links (since there is a new one row for each shop).

i.e.
id link_id shop_id

19 5 1
18 3 1
20 4 1
21 2 1
23 3 2
25 4 2
26 2 2

then in links:

id title link

1 Air France http://asdasd.com/...
2 T Mobile http://asdsad.com/...
3 Carph... http://asdsad.com/...
4 Phones4u http://asdsda.com/...
5 Excell... http://asdasd/...

Hopefully you can see how the two relate?

Any ideas? I'd really appreciate some help here, it's been going on for days... 🙁

Thanks,

Chris Evans

common_ground

does is make any difference using brackets
SELECT links.id, links.title, relatedlinks.id, relatedlinks.link_id, relatedlinks.shop_id
FROM links
LEFT JOIN relatedlinks

ON links.id = relatedlinks.link_id

WHERE (relatedlinks.shop_id = '2' OR relatedlinks.shop_id IS NULL) AND links.id != '5'
ORDER BY links.title ASC LIMIT 0 , 30

Chris_Evans

Unfortunately not. I've filled the relatedlinks database so there are rows in there that match the link_id with links.id but they aren't related to the shop and obviously aren't null either - so that only returns the result of a shop.