MySQL Query problem

[Chris_Evans]

Hi there,
The following query:

SELECT links.id, links.title, relatedlinks.id, relatedlinks.link_id, relatedlinks.shop_id FROM links, relatedlinks ORDER BY links.title ASC

Table 'links':
- id
- title
- link

Table 'relatedlinks':
- id
- link_id
- shop_id

It doesn't work quite the way it should though, and although I can see why, I can't see the fix.

The way this works, is that it retrieves all the links from the database, putting a tick in each checkbox where:

link.id == relatedlinks.link_id && relatedlinks.shop_id == $shop_id

Any ideas?

Thanks,

Chris

[Chris_Evans]

Woops - wrong forum - just realised. Can this be moved to databases please?

Thanks,

Chris

[dewen]

should it be a link between two tables:

SELECT links.id, links.title, relatedlinks.id, relatedlinks.link_id, relatedlinks.shop_id

FROM links, relatedlinks

where links.id=relatedlinks.link_id
ORDER BY links.title ASC

?

[Chris_Evans]

Hi there,
That helps. I've got this and it works, thanks:

SELECT links.id, links.title, relatedlinks.link_id, relatedlinks.shop_id
FROM links
LEFT JOIN relatedlinks ON links.id = relatedlinks.link_id
WHERE relatedlinks.shop_id = '1' OR relatedlinks.link_id IS NULL  LIMIT 0 , 30

Thanks!

Chris