CODEWITHSUNDEEP
mysqlsql
Rowan San
Rowan San

Reputation: 1577

Joining two table with OR clause

I have a problem with my query results. I have 2 tables that I want to join with specific join to get all the informations about it and a or condition that doesnt include second table. There is my tables :

main_orders:

id | destination
----------
1  |  London
2  |  Germany
2  |  Netherland
3  |  Polska
4  |  JP

includes:

id | rel_id
----------
1  |  2
1  |  3

here id number 1 is the main order and it also covers the rest of the orders show as in the second table with rel_id

i want to select the order details of id 1 from main_orders and also the orders that relate to this id

my query is,

SELECT a.id FROM main_orders a, includes b
where (a.id = 1) OR (b.id = 1 and a.id = b.rel_id)

it works only when there is any relative orders in the second table, please help the result should be as follows

RESULTANT ROWS:

id | destination
----------
1  |  London
2  |  Germany
2  |  Netherland
3  |  Polska

Thanks

Upvotes: 0

Views: 94

Answers (4)

mjr
mjr

Reputation: 2113

Perhaps something like this?

SELECT a.id FROM a LEFT JOIN b ON a.id=b.id GROUP BY a.id;

(obviously use your table names)

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

you could use an exists clause:

SELECT a.id, a.destination FROM 
main_orders a
where a.id = 1 
or exists (select null 
                          from includes b 
                          where  b.rel_id = a.id
                          and b.id =1);

see sqlFiddle

Upvotes: 3

Lucian0c
Lucian0c

Reputation: 13

Try this.

SELECT a.id FROM main_orders a, includes b
where (b.id=1 and (a.id=b.rel_id or a.id=1 ))

Upvotes: 0

eggyal
eggyal

Reputation: 126035

SELECT      main_orders.id, main_orders.destination
FROM        main_orders
  LEFT JOIN includes ON includes.rel_id = main_orders.id
WHERE       main_orders.id = 1 OR includes.id = 1

See it on sqlfiddle.

Upvotes: 0

Related Questions