Jacco Jacco - 4 months ago 8
SQL Question

innerjoin or another way to combine data

Out of a database I am trying to combine two tables into one search query.

Table1: faq

| faq_id | title | status | url_key |
| ------ | ----- | ------ | ------- |
| 1 | Test | 1 | go.html |


Table2: faq_value

| faq_value_id | faq_id | attribute | value |
| ------------ | ------ | --------- | ------- |
| 1 | 1 | metakey | testing |
| 2 | 1 | metadesc | this is a test |
| 3 | 1 | meta_image | test.jpg |


Now my desired query result would look like this:

| faq_id | title | status | metadesc | metakey | meta_image | url_key |
| ------ | ----- | ------ | -------- | ------- | ---------- | ------- |
| 1 | Test | 1 | this is a | testing | test.jpg | go.html |


My attempt so far has failed:

SELECT
faq.faq_id,faq.title,status,
(SELECT faq_value.value AS metadesc WHERE faq_value.attribute_code = 'metadesc'),
(SELECT faq_value.value AS metakey WHERE faq_value.attribute_code = 'metakey'),
(SELECT faq_value.value AS meta_image FROM faq_value WHERE faq_value.attribute_code = 'meta_image')
faq.url_key,
FROM faq
INNER JOIN faq_value ON faq_value.faq_id = faq.faq_id


I am sure I am just overlooking something silly, but just cannot spot is. This one kicks a SQL error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE faq_value.attribute_code = 'metadesc'), (SELECT faq_value.value AS metakey' at line 2

Answer

You can use 3 inner join on the faq_value table

   SELECT 
    faq.faq_id
  , faq.title
  , faq.status
  , b.value as metadesc
  , c.value as metakey
  , d.value as metakey
  , faq.url
from faq
INNER JOIN faq_value as b on  b.faq_id = faq.faq_id and b.attribute_code = 'metadesc'
INNER JOIN faq_value as c on  c.faq_id = faq.faq_id and c.attribute_code = 'metakey'
INNER JOIN faq_value as d on  d.faq_id = faq.faq_id and d.attribute_code = 'meta_image'    
Comments