MySQL Join Query Problem

Question

I have the following tables.

PHONES
 - id
 - name
 - colour
 - size

STOCK
 - store_id
 - phone_id
 - instock

STORES
 - id
 - name

LINKS
 - store_id
 - phone_id
 - url

I want to SELECT name, colour, size from PHONES, instock from STOCK, name from STORES, and url from LINKS WHERE stock.phone_id = phones.id = links.phone_id AND stock.store_id = links.store_id AND stores.id = stock.store_id.

I want out put, phone.name, phone.colour, phone.size, stock.instock, stores.name, links.url.

This is my current query. I am not getting specific links back related to that store AND phone id's.

SELECT DISTINCT(s.phone_id), st.name, st.logo_url, l.url, s.instock, s.datetime, p.model, p.colour, p.size 
FROM stock AS s, stores AS st 
INNER JOIN links AS l, phones AS p 
WHERE s.phone_id = p.id 
AND s.phone_id = l.phone_id 
AND s.store_id = l.store_id 
AND s.store_id IN ('3','4','5','6','8') 
AND s.phone_id = '5' 
ORDER BY datetime 
DESC LIMIT 5

Answer 1

SELECT p.name, p.colour, p.size, st.instock, s.name, l.url
FROM phones p
INNER JOIN links l ON l.phone_id = p.id
INNER JOIN stores s ON s.id = l.store_id
INNER JOIN stock st ON st.store_id = s.id AND st.phone_id = p.id
WHERE s.id IN ('3', '4', '5', '6', '8')
AND p.id = '5'

datetime isn't defined in your table description so I neglected to add it to the query, but this should provide what you are looking.