CODEWITHSUNDEEP
mysqlsql
user1212368
user1212368

Reputation: 21

How to SELECT over two tables?

After spending quite a lot of time trying and soing some resarch i finally surrender and ask you folks for your help. This is what my MySQL Tables look like:

Table1:

Parent_ID | ID | Name | Quality |Price
1         | 001|Apple | good    |1.50
1         | 002|Apple | medium  |1.20
1         | 003|Apple | poor    |0.99
2         | 004|Car   | good    |5000.00
2         | 005|Car   | poor    |200.00

Table2:

Parent_ID | Var_Name | Value
1         | color    |  red
1         | size     |  big
1         | rating   |  3 Star
2         | color    |  blue
2         | size     |  medium
2         | rating   |  ok

Ok.So far so good. What i need now is a query that gives me this:

Parent_ID | ID  | Name  | Quality |   Price | color | size   | rating
1         | 001 | Apple | good    |    1.50 | red   | big    | 3Star
1         | 002 | Apple | medium  |    1.20 | red   | big    | 3Star
1         | 003 | Apple | poor    |    0.99 | red   | big    | 3Star
2         | 004 | Car   | good    | 5000.00 | blue  | medium | ok
2         | 005 | Car   | poor    |  200.00 | blue  | medium | ok

Upvotes: 2

Views: 252

Answers (4)

mankuTimma
mankuTimma

Reputation: 106

There is one more way to do it. It uses only one join but you have to use aggregate functions.

SELECT table1.*, 
max(if(table2.var_name='color',table2.value, NULL)) as color,
max(if(table2.var_name='size',table2.value, NULL)) as size,
max(if(table2.var_name='rating',table2.value, NULL)) as rating,
FROM table1 join table2 
WHERE table1.ID = table2.ID
GROUP BY table.*

Upvotes: 0

Conrad Frix
Conrad Frix

Reputation: 52645

Just join to table2 three times filtering for var_name in the JOIN clause. 

SELECT t.parent_id, 
       t.id, 
       t.name, 
       t.quality, 
       t.price, 
       c.VALUE AS color, 
       s.VALUE AS size, 
       r.VALUE AS rating 
FROM   table1 t 
       LEFT JOIN table2 c 
         ON t.parent_id = c.parent_id 
            AND c.var_name = 'color' 
       LEFT JOIN table2 s 
         ON t.parent_id = s.parent_id 
            AND s.var_name = 'size' 
       LEFT JOIN table2 r 
         ON t.parent_id = r.parent_id 
            AND r.var_name = 'rating'

Upvotes: 5

Chad
Chad

Reputation: 7507

Select T1.Parent_ID, T1.ID, T1.Name, T1.Quality, T1.Price,
    (select value from Table2 T2 where T2.Parent_ID = T1.Parent_ID and Var_Name = 'color') as color,
    (select value from Table2 T2 where T2.Parent_ID = T1.Parent_ID and Var_Name = 'size') as size, 
    (select value from Table2 T2 where T2.Parent_ID = T1.Parent_ID and Var_Name = 'rating') as rating
FROM Table1 T1

Upvotes: 1

shuniar
shuniar

Reputation: 2622

All you need is a simple join I believe...

SELECT *
FROM Table1 t1
JOIN Table2 t2
  ON t1.Parent_Id = t2.Parent_Id

Upvotes: -1

Related Questions