0

I have a Table pages and two other ones called page_options and page_option_values which make optional options possible without modify the table layout.

The tables have the following structure (shortend):

pages:
 * page_id
 * name

page_options:
 * page_option_id
 * name

page_option_values:
 * page_id
 * page_option_id
 * value

Can I get a result from MySQL which selects all columns from pages and shows the name column from page_options as virtual column with value from page_option_values?

EDIT:

I have the following Query now - but don't now why it don't work?

SELECT
 n.page_id,
 n.page_name,
 a.column_name,
 v.value as a.column_name
FROM pages n
JOIN page_attribute_value v on v.page_id = n.page_id
JOIN page_attributes a on a.attribute_id = v.attribute_id
nPoday
  • 95
  • 1
  • 10
  • 1
    "Can i get a Result from MySQL which selects all Columns from pages and shows the name column from page_options "as virtual column" with value from page_option_values?" Yep using left joins. and dynamic SQL if you have an undeterministic number of name virtual columns – xQbert Jun 10 '14 at 20:48
  • 1
    This is called "pivoting". If you don't know the column names ahead of time, you have to do it using dynamic SQL. – Barmar Jun 10 '14 at 20:49
  • @Barmar I got until this now: "SELECT n.`navigation_item_id`, n.`parent_id`, n.`title`, n.`content_type`, n.`content_id`, n.`template`, n.`sort`, nio.`column_name`, niov.`value` as nio.`column_name` FROM pages as n JOIN page_item_options nio on nio.page_item_option_id = n.page_item_id JOIN page_item_option_value niov on niov.page_item_option_id = nio.page_item_option_id" But it doesn't work? :( – nPoday Jun 10 '14 at 22:05
  • Please post a nicely formatted query in the question. It's very difficult to read code in comments. – Barmar Jun 11 '14 at 04:19
  • @Barmar Updated Question with formated Query – nPoday Jun 11 '14 at 08:19
  • @xQbert I have build a query but it doesn't work - can you have a look too? – nPoday Jun 11 '14 at 08:19
  • You can't use `a.column_name` as an alias. – Barmar Jun 11 '14 at 14:52

1 Answers1

0

Just to get you started you need the left joins

You laid out the tables as Page_Options and page_option_values is this correct or is the page_attribute instead of option?

Select p.page_Id, p.name, po.page_option_Id, PO.name, POV.value
from pages P
LEFT JOIN page_option_Values POV
 on P.page_ID = POV.Page_Id
LEFT JOIN page_option PO
 on PO.Page_OptioN_ID = POV.Page_Option_ID
xQbert
  • 34,733
  • 2
  • 41
  • 62