Jump to content

Help with combining 2 queries


jason33

Recommended Posts

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

 

Link to comment
https://forums.phpfreaks.com/topic/241732-help-with-combining-2-queries/
Share on other sites

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

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!

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]'

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.