CODEWITHSUNDEEP
mysqlsql
rpirez
rpirez

Reputation: 431

How Union two separate queries in one simple query - MySQL

I want to union two separate queries in one simple query. How Can I do that?

The queries:

select idProduct, date,price from products where idProduct like 'Prod01'

idProduct             Date         Price
    Pro01           2014-05-29      19.1
    Pro01           2014-05-29      18.8
    Pro01           2014-05-29      18.7
    Pro01           2014-05-29      18.9
    Pro01           2014-05-29      18.7
    Pro01           2014-05-29      18.5

select idProduct, date,price from products where idProduct like 'Prod02'

idProduct          Date         Price
Pro02           2014-05-29      29.1
Pro02           2014-05-29      28.8
Pro02           2014-05-29      28.7
Pro02           2014-05-29      28.9
Pro02           2014-05-29      28.7
Pro02           2014-05-29      28.5

I want that price off product 'Prod02' is in a fourth column.

I explained well?

Basically I need something like this:

idProduct                     Date         Price of 'Prod01'   Price of 'Prod02'
   Pro01 / Pro02           2014-05-29      19.1                 28.1
   Pro01 / Pro02           2014-05-29      18.8                 26.1
   Pro01 / Pro02           2014-05-29      18.7                 24.1
   Pro01 / Pro02           2014-05-29      18.9                 22.1
   Pro01 / Pro02           2014-05-29      18.7                 21.1
   Pro01 / Pro02           2014-05-29      18.5                 22.1

Upvotes: 0

Views: 99

Answers (2)

Serpiton
Serpiton

Reputation: 3684

If you have different timestamp in the date column a JOIN can be used the get the data (with every row with only the date, and always the same it will not work)

SELECT concat(p1.idProduct, ' / ', p2.idProduct) idProduct
     , p1.`Date`
     , p1.`Price` as `Price of Prod01`
     , p2.`Price` as `Price of Prod02`
FROM   (SELECT idProduct, Date, Price
        FROM products
        WHERE idProduct LIKE 'Pro01') p1
       LEFT  JOIN (SELECT idProduct, Date, Price
                   FROM products
                   WHERE idProduct LIKE 'Pro02') p2
       ON p1.Date = p2.Date

SQLFiddle Demo

If the row of the two product are to be get in the same order (which order?) you can use the MySQL version of Row_Number

SELECT concat(p1.idProduct, ' / ', p2.idProduct) idProduct
     , p1.`Date` `Date of Prod01`
     , p2.`Date` `Date of Prod02`
     , p1.`Price` as `Price of Prod01`
     , p2.`Price` as `Price of Prod02`
FROM   (SELECT idProduct, Date, Price
             , @rownum := @rownum + 1 AS ID
        FROM products,
             (SELECT @rownum := 0) r
        WHERE idProduct LIKE 'Pro01') p1
       LEFT  JOIN (SELECT idProduct, Date, Price
                        , @rownum2 := @rownum2 + 1 AS ID
                   FROM   products,
                          (SELECT @rownum2 := 0) r
                   WHERE idProduct LIKE 'Pro02') p2
       ON p1.ID = p2.ID

SQLFiddle demo

But I really hope your timestamp is not only the date.

Upvotes: 0

Marion
Marion

Reputation: 71

SELECT *
FROM(
SELECT idProduct, Date,(Price) as Price01, '0' as Price02
FROM products
WHERE idProduct LIKE 'Prod01'
UNION
SELECT idProduct, Date,'0' as Price01, (Price) as Price02
FROM products 
WHERE idProduct like 'Prod02'
) as temp

Upvotes: 1

Related Questions