CODEWITHSUNDEEP
mysqljoin
InTry
InTry

Reputation: 1169

mysql associative table to multiple tables

I have tables like this:

      map           table1           table2           table3
| aID | bID |   | bid | val |     | bid | val |    | bid | val |
-------------   -------------     -------------    -------------
|  1  |  1  |   |  1  |  20 |     |  3  |  10 |    |  4  |  15 |
-------------   -------------     -------------    -------------
|  1  |  2  |   |  1  |  22 |     |  3  |  23 |    |  4  |  25 |
-------------   -------------     -------------    -------------
|  1  |  3  |   |  2  |  15 |     |  3  |  14 |    |  4  |  11 |
-------------   -------------     -------------    -------------
|  2  |  3  |   |  2  |  23 |     |  5  |  32 |    |  2  |  3  |
-------------  
|  2  |  4  |
-------------

tried like this:

SELECT val, bid 
   FROM map m
   RIGHT JOIN table1 t 
     ON  m.bID =t.bid
   RIGHT JOIN table2 tt 
     ON  m.bID =tt.bid
   RIGHT JOIN table3 ttt  
     ON  m.bID =ttt.bid
   WHERE aID = 1

bID can be just in one of tableX

is there is an easy way to retrieve all values for aID 1?

Upvotes: 0

Views: 76

Answers (1)

miltos
miltos

Reputation: 1019

Try this query

SELECT val, bid 
FROM map m
LEFT JOIN table1 t 
    ON  m.bID =t.bid
LEFT JOIN table2 tt 
    ON  m.bID =tt.bid
LEFT JOIN table3 ttt  
    ON  m.bID =ttt.bid
WHERE aID = 1

ok, based, on you comments I edit my post, try this

SELECT val, bid 
FROM map m
LEFT JOIN table1 t 
    ON  m.bID =t.bid
WHERE aID = 1
UNION
SELECT val, bid 
FROM map m
LEFT JOIN table2 tt
    ON  m.bID =tt.bid
WHERE aID = 1
UNION
SELECT val, bid 
FROM map m
LEFT JOIN table3 ttt  
    ON  m.bID =ttt.bid
WHERE aID = 1

Upvotes: 1

Related Questions