ritesh khadka ritesh khadka - 4 months ago 11
SQL Question

How to get single value from second table if data exists else from first table?

I have two tables. I want to get the latest updated_product_id if exists in second table else product_id from first table.
I have first table sr_client_products

id | product_id| client_id
1 | 1 | 1


and second table sr_client_product_updates

id | product_id| updated_product_id | client_product_id| updated_date
1 | 1 | 2 | 1 | 2016-02-03
2 | 2 | 4 | 1 | 2016-06-07


**Expected Output: Single row column

updated_product_id = 4 as of updated_date


and if second table sr_client_product_updates has no value then

product_id = 1 from first table


.**
Till now query

SELECT
if(ProductUpdate.updated_product_id = '',
(select ClientProduct.product_id from sr_client_products ClientProduct where ClientProduct.client_id=1),
ProductUpdate.updated_product_id)
as product_id
from sr_client_product_updates ProductUpdate where ProductUpdate.client_product_id=1 order by ProductUpdate.updated_date DESC limit 1


The problem with the above query is it shows data if value exists in second table else returns empty and I think the problem is with where query also
Output:
product_id = 4 if data exists in second table
else
product_id =1 if second table is empty

Edited Query:

SELECT
if(count(*) = 0,
(select ClientProduct.product_id from sr_client_products ClientProduct where ClientProduct.client_id=1),
ProductUpdate.updated_product_id)
as product_id
from sr_client_product_updates ProductUpdate
where ProductUpdate.client_product_id=1
order by ProductUpdate.updated_date DESC limit 0,100


SOLUTION As GIVEN BY @Philipp

SELECT
COALESCE (
sr_client_product_updates.updated_product_id,
sr_client_products.product_id
) AS id
FROM
sr_client_products
LEFT JOIN (
SELECT
MAX(updated_date) AS update_date,
client_product_id
FROM
sr_client_product_updates
GROUP BY
client_product_id
) AS lastUpdate ON lastUpdate.client_product_id = sr_client_products.id
LEFT JOIN sr_client_product_updates ON sr_client_product_updates.client_product_id = lastUpdate.client_product_id
AND sr_client_product_updates.updated_date = lastUpdate.update_date where sr_client_products.id=1

Answer
SELECT
    COALESCE (
        sr_client_product_updates.updated_product_id,
        sr_client_products.product_id
    ) AS id
FROM
    sr_client_products
LEFT JOIN (
    SELECT
        MAX(updated_date) AS update_date,
        client_product_id
    FROM
        sr_client_product_updates
    GROUP BY
        client_product_id
) AS lastUpdate ON lastUpdate.client_product_id = sr_client_products.client_product_id
LEFT JOIN sr_client_product_updates ON sr_client_product_updates.client_product_id = lastUpdate.client_product_id
AND sr_client_product_updates.updated_date = lastUpdate.update_date

Returns the updated product_id if there is one corresponding to the actual product_id. If there is no updated id, it returns the product_id.

Though I am not sure which columns you need to use for the join, product_id would be the obvious choice but your example makes it a little weird. So you might need to fix the ON clause.

Fiddle for example data

Fiddle for empty second table