Help with alias

[gvp16]

Hi, i have 2 tables

 

content which has : content_id, content_title,content_body

eg. (1,this is a test, information about the test)

 

and

 

content_options which as : content_id, option, value

eg.

(1,price,29.99)

(1,weight,15kg)

(1,sale,yes)

 

i know you can do something like, SELECT content_options.value AS price WHERE content_options.option = "price"

 

which would give me

 

price

29.99

 

by how can i join to the two in a single query? eg. get a result like :

 

 

 

 

content_id | content_title |  content_body                                |  price    |  weight | sale

1                  this is a test    this is informaton about the test      29.99      15kg      yes

 

 

Thanks.

[requinix]

Two methods. The one I prefer is to use some JOINs

SELECT c.content_id, c.content_title, c.content_body, co_p.value AS price, co_w.value AS weight, co_s.value AS sale
FROM content c
LEFT JOIN content_options co_p ON c.content_id = co_p.content_id AND co_p.option = "price"
LEFT JOIN content_options co_w ON c.content_id = co_w.content_id AND co_w.option = "weight"
LEFT JOIN content_options co_s ON c.content_id = co_s.content_id AND co_s.option = "sale"

(outer joins, just in case the price/weight/sale value is missing) and the other is subqueries

SELECT c.content_id, c.content_title, c.content_body,
    (SELECT value FROM content_options co WHERE co.content_id = c.content_id AND co.option = "price") AS price,
    (SELECT value FROM content_options co WHERE co.content_id = c.content_id AND co.option = "weight") AS weight,
    (SELECT value FROM content_options co WHERE co.content_id = c.content_id AND co.option = "sale") AS sale,
FROM content c

[gvp16]

Excellent, thanks very much, i spent a few hours yesterday trying to figure it out!

 

Is there one method you would pick out of the 2? eg. performance etc...

 

Thanks.

[Muddy_Funster]

Use JOINS over subqueries.