Using JOIN sql query

Question

I would like to use JOIN instead of IN in the following SQL query. I can't figure out how to do it.

SELECT * FROM shop_orders WHERE 
id IN (SELECT orders_id FROM shop_orders_data WHERE closed='1' /*AND backorder='0'*/  AND   exhibition_id='389' AND 
exhibition_id IN (SELECT id FROM shop_exhibitions WHERE 
country_id IN (SELECT id FROM countries WHERE id='72')) AND in_country = '72' AND 
exhibition_id IN (SELECT id FROM shop_exhibitions WHERE start<=1336946400 AND end>1336600800)) AND 
id IN (SELECT orders_id FROM shop_orders_products WHERE 
products_id IN (SELECT id FROM shop_products WHERE artno='120000' OR name LIKE '%120000%')) AND   created>=1333231200 AND created<1333663200 ORDER BY created DESC

I tried this:

SELECT 
s.* 
FROM 
shop_orders s 
INNER JOIN shop_orders_data od ON s.id=od.orders_id
INNER JOIN shop_exhibitions se ON od.exhibition_id=se.id 
INNER JOIN countries co ON se.country_id=co.id 
INNER JOIN shop_orders_products sop ON s.id=sop.orders_id 
INNER JOIN shop_products sp 
 ON sop.products_id=sp.id 
WHERE od.closed=1
AND ( sp.artno='120000' or sp.name LIKE '%120000%' )
AND ( od.exhibition_id='389')
AND ( od.in_country = '72')
AND ( se.start <=1336946400)
AND ( se.end >1336600800)
AND ( se.created>=1333231200)
AND ( se.created<1333663200)
ORDER BY `s`.`created` DESC

I this correct??

Have you considered reading the documentation on the JOIN syntax first? — JohnFx
– JohnFx, Commented Jun 6, 2012 at 21:12

J. Bruni · Accepted Answer · 2012-06-07 15:37:27Z

2

See if this works (and study the code to learn how it works):

SELECT * 
FROM shop_orders so
JOIN shop_orders_data sod ON (
    (so.id = sod.orders_id)
    AND (sod.closed = '1')
    /*AND (sod.backorder = '0') */
    AND (sod.exhibition_id = '389')
    AND (sod.in_country = '72')
)
JOIN shop_exhibitions se ON (
    (sod.exhibition_id = se.id)
    AND (se.start <= 1336946400)
    AND (se.end > 1336600800)
)
JOIN countries c ON (
    (se.country_id = c.id)
    AND (c.id = '72')
)
JOIN shop_orders_products sop ON (
    (so.id = sop.orders_id)
)
JOIN shop_products sp ON (
    (sop.products_id = sp.id)
    AND ((sp.artno='120000') OR (sp.name LIKE '%120000%'))
)
WHERE (so.created >= 1333231200) AND (so.created < 1333663200)
ORDER BY so.created DESC;

edited Jun 7, 2012 at 15:37

answered Jun 6, 2012 at 21:22

J. Bruni

20.5k13 gold badges80 silver badges93 bronze badges

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

2 Comments

RonLugge Over a year ago

If not from him, then at least from me.

Oualid Over a year ago

Hi J. Bruni. Of course you earn a lot of votes. I would lovely to do it, but how do I do it?

JohnFx · Accepted Answer · 2012-06-06 21:15:34Z

1

The join syntax works like this:

SELECT field1,field2,field3
FROM FirstTable 
    JOIN SecondTable ON (FirstTable.PrimaryKey = SecondTable.ForeignKey)
    JOIN ThirdTable ON (FirstTable.PrimaryKey = ThirdTable.ForeignKey)

Try applying this approach to your query.

answered Jun 6, 2012 at 21:15

JohnFx

35k19 gold badges108 silver badges169 bronze badges

Collectives™ on Stack Overflow

Using JOIN sql query

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related