Mysql, data spread over two tables, seperate rows

开发者_C百科I have data spread over two MySQL tables with different structures.

One table has DVDs and the other has CDs.

The DVD table is as follow:

PUBLISHER
STOCK
DVD_INFO
EXTRA_DVD_INFO

The CDs table is as follow:

PUBLISHER
STOCK
CD_INFO

How do you get all the CDs and DVDs by the same publisher in one query, ordered by STOCK?

• One row per product.
• If it's a CD, then the DVD specific fields should be empty.
• If it's a DVD, then the CD specific fields should be empty.

I don't think UNION can work because the structures are different.

I'm not sure how JOIN could work in this case to get separate rows for each product.

---

You can use NULL's to fill in the empty columns, eg.

SELECT
    PUBLISHER,
    STOCK,
    DVD_INFO AS INFO,
    EXTRA_DVD_INFO
FROM DVD
WHERE PUBLISHER = ?
    UNION ALL
SELECT
    PUBLISHER,
    STOCK,
    CD_INFO AS INFO,
    NULL
FROM CD
WHERE PUBLISHER = ?
ORDER BY STOCK;