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

anothershrubery · Accepted Answer · 2011-03-18 16:50:23Z

3

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.

edited Mar 18, 2011 at 16:50

answered Mar 18, 2011 at 16:30

anothershrubery

21.1k16 gold badges56 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ajay87 Over a year ago

Thanks anothershrubery. The query you suggested does bring back almost what i would like, however it brings back listings for only ONE of the stores in the where clause. Going to have a look at it now, but any suggestions would be welcome.

anothershrubery Over a year ago

If you just copy/pasted my query above, the problem would be that I put in some full stops instead of commas! Try the updated query above.

anothershrubery Over a year ago

Actually if you copy/pasted the previous query, it shouldn't have ran at all! Without knowing the data in your database I can't see why it wouldn't return all stores with them id's. Try without the WHERE clause and see what id's are returned. (Obviously adding s.id to the returned fields.)

ajay87 Over a year ago

Yeah noticed the errors with the .'s and query now also includes the WHERE clause (my mistake). I'm getting data returned fine. However just not in the right order it seems. With the query below i get back 5 rows, but the store name is the same, and links diff.

SELECT p.model, p.colour, p.size, st.instock, s.name, l.url, st.datetime FROM phones AS p INNER JOIN links AS l ON l.phone_id = p.id INNER JOIN stores AS s ON s.id = l.store_id INNER JOIN stock AS 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' ORDER BY st.datetime DESC LIMIT 5

anothershrubery Over a year ago

In your links table do you have multiple stores of the same id?

|

Collectives™ on Stack Overflow

MySQL Join Query Problem

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related