CODEWITHSUNDEEP
mysql
George Woodward
George Woodward

Reputation: 81

MySQL - Similar headings - multiple tables

I have 2 tables, one containing the main information with a second table containing the same fields plus additional. The second table would have data which should overwrite the first table when queried. The fields are bigger, I've just shortened them down to show here.

First table (products):

+----+--------------+--------------+---------------+--------------------------+
| id | manufacturer | product_name | product_title |      product_description |
+----+--------------+--------------+---------------+--------------------------+
|  1 |     testingA |     productA | productTitleA | main product description |
|  2 |     testingA |     productB | productTitleB | main product description |
|  3 |     testingA |     productC | productTitleC | main product description |
+----+--------------+--------------+---------------+--------------------------+

Second Table (products_secondary)

+----+------------+--------------+--------------+---------------+----------------------+---------+
| id | product_id | manufacturer | product_name | product_title |  product_description | context |
+----+------------+--------------+--------------+---------------+----------------------+---------+
|  1 |          1 |       (null) |       (null) |        (null) | new description here |    test |
+----+------------+--------------+--------------+---------------+----------------------+---------+

My aim is to select the values from the second table where context = text

Expected result:

+----+--------------+--------------+---------------+--------------------------+
| id | manufacturer | product_name | product_title |      product_description |
+----+--------------+--------------+---------------+--------------------------+
|  1 |     testingA |     productA | productTitleA | new description here     |
|  2 |     testingA |     productB | productTitleB | main product description |
|  3 |     testingA |     productC | productTitleC | main product description |
+----+--------------+--------------+---------------+--------------------------+

The query which I know works is the following:

SELECT IFNULL(`products_secondary`.`product_description` , `products`.`product_description`) AS `product_description`
FROM `products`
LEFT JOIN `products_secondary` ON `products_secondary`.`product_id` = `products`.`id` AND `products_secondary`.`context` = 'test'

I'm sure there is an easier way to do this than having to supply IFNULL() for each field. Here is an SQL Fiddle: http://sqlfiddle.com/#!9/6985b/1

Thanks.

Upvotes: 0

Views: 25

Answers (1)

mynawaz
mynawaz

Reputation: 1594

Use this query

SELECT a.id, a.manufacturer, a.product_name, a.product_title, IF(b.context IS NOT NULL, b.product_description, a.product_description) as product_description
FROM
    products a
    LEFT OUTER JOIN products_secondary b ON a.id=b.product_id

Upvotes: 1

Related Questions