MySQL alternate row selects

Question

Lets say I have data in two tables. In one I have Order ID and Order Date. In the other I have Order ID, Description and Value. I know how to do simple SELECTs but what would I use to output something like this?

Order ID | Order Date | Description                     | Value
1234     | 10/07/2009 |                                 |
         |            | Orderline description goes here | 53.49
         |            | Orderline description goes here | 25.63
         |            | Orderline description goes here | 21.64
12345    | 11/07/2009 |                                 |
         |            | Orderline description goes here | 12.37
         |            | Orderline description goes here | 13.86
         |            | Orderline description goes here | 17.79

Answer 1

I would use a regular join, omitting repeating values and grouping can be handled the the presentation layer (e.g. GUI).

Answer 2

Supposing you maintain a foreign key constraint between TABLE1 and TABLE2 on ORDER_ID field....

SELECT t1.ORDER_ID as [ORDER ID],
       t1.ORDER_DATE as [ORDER DATE],
       t2.DESCRIPTION,
       t2.VALUE
FROM 
       TABLE1 t1 INNER JOIN TABLE2 t2 ON
              t1.ORDER_ID = t2.ORDER_ID

Then on the GUI ignore the repeating values...

OR you can take UNIONS...

SELECT ORDER_ID as [ORDER ID],
       ORDER_DATE as [ORDER DATE],
       NULL as DESCRIPTION,
       NULL as VALUE
FROM TABLE1
     ORDER BY ORDER_ID

UNION ALL

SELECT NULL as [ORDER ID],
       NULL as [ORDER DATE],
       DESCRIPTION,
       VALUE
FROM TABLE2
     ORDER BY ORDER_ID

but you still have to do few thing on the GUI.

Answer 3

Assuming table1, table2 are the tables. You can do

select OrderId, OrderDate, Description, Value from Table1,Table2 where Table1.OrderId = Table2.OrderId

The OrderId is used to match the records in both the tables in the where clause.

Answer 4

Expanding upon Josef's answer, a query like the following may serve as the basis of your solution:

SELECT `Order Id`, `Order Data`, `Description`, `Value` FROM `OrderHeader` INNER JOIN `OrderItem` ON `OrderHeader`.`Order Id` = `OrderItem`.`Order Id` ORDER BY `Order Id`

(Where OrderHeader is assumed to be the name of your first table, and OrderItem is assumed to be the name of your second). Ordering by Order Id will group rows from the same order together. As Josef says, the rest of the presentation will be up to the application to do.

Answer 5

This really should be done on the client side, but here is the possible solution:

SELECT  IF(od.order_id > 0, NULL, o.order_id),
        IF(od.order_id > 0, NULL, o.order_date),
        od.description, od.value
FROM    order o
JOIN    order_data od
ON      od.order_id = o.order_id
GROUP BY
        o.order_id, od.order_data_id WITH ROLLUP