Help with combining 2 queries

[jason33]

Hi all,

I need to combine two queries. Problem is the two queries collect data from the same table and the same column name. The table is called 'order_options' and the column name is 'value'. I need to pull out the values in this column and separate them into two, based on a related column 'name. Here are my 2 queries:

 

SELECT o.value AS sdate, o.order_product_id
    FROM order_option o
        INNER JOIN order_product p ON o.order_product_id = p.order_product_id 
    WHERE 
        p.product_id = '$_GET[u]'
        AND o.name = 'Start Date'
    ORDER BY o.order_product_id ASC

 

SELECT o2.value AS sday
FROM order_option o2
	INNER JOIN order_product p ON o2.order_product_id = p.order_product_id 
WHERE 
    p.product_id = '$_GET[u]'
	AND o2.name = 'Number of Days' 
ORDER BY o2.order_product_id ASC

 

I have tried UNION. but this just combines the results for the two 'value' columns, they need to stay separate. Any help would be appreciated.

 

Many thanks,

Jason

 

[gizmola]

The cleanest way would be to include the o2.name, so you can tell per row what is in the o.value rather than trying to create 2 separate columns.

 

SELECT o.value AS odate, o.name as odatetype, o.order_product_id
    FROM order_option o
        INNER JOIN order_product p ON o.order_product_id = p.order_product_id 
    WHERE 
        p.product_id = '$_GET[u]'
        AND (o.name = 'Start Date' OR o.name = 'Number of Days')
    ORDER BY o.order_product_id ASC

[jason33]

Thanks this might work, but the information I am pulling out from the two sets of data is a date (on my first example 'o.value AS sdate' ) and a number of days (o2.value AS sdays). They need to be separate as I need to add the number of days to the date to create an "end" date, I can do this if they come out as separate variables, but my php isn't good enough to do that from using the above!

[jason33]

This almost works, but using MIN only returns one set of values when there should be several...

 

SELECT 
    MIN(CASE WHEN o.name = 'Start Date' THEN o.value END) AS sdate,
    MIN(CASE WHEN o.name = 'Number of Days' THEN o.value END) AS sday,
o.order_product_id
FROM order_option o
    INNER JOIN order_product p USING (order_product_id)
WHERE 
    p.product_id = '$_GET[u]'