Q:

need help with tricky mySQL query

I'm asking for an awful lot here - but maybe some SQL guru can show me how to extract the data I want and save me 10+ hours of google-time(tm)?
These are my tables, with only relevant fields displayed:
**event**
id
cust_id
....

.
**art**
id
art_name
...

.
**event_art**
event_id
art_id
...

.
**price**
cust_id
art_id
price
...

Prices in the "price" DB with user ID "0" is standard price, if an entry exists with art_id and cust_id that is customer specific price for that article.
What I have is cust_id and what I have for output now is just the customer specific prices with SQL:
SELECT * FROM price WHERE cust_id='{$custID}' 

But I'd like to include prices for previously ordered articles, even if they do not have a customer specific price.
So what I need is to:
1 Get all id's from table event where cust_id = custID
2 Get all distinct article ID's on those orders from table event_art
3 Output "id" and "art_name" of article from "art" and "price" from price table using custID or 0 for standard price if no entry exists.
To me this sounds like a multi-line JOIN that's a bit outside my scope of SQL knowledge.  Could somebody help me out, point me to a guide that deals with similar issues or... well, something?
Thanks in advance!

A:

SELECT  art_id, price
FROM    price
WHERE   cust_id = $cust_id
UNION ALL
SELECT  art_id, price
FROM    (
        SELECT  DISTINCT art_id
        FROM    event e
        JOIN    event_art ea
        ON      ea.event_id = e.id
        WHERE   e.cust_id = $cust_id
                AND ea.art_id NOT IN
                (
                SELECT  art_id
                FROM    price
                WHERE   cust_id = $cust_id
                )
        ) e
JOIN    price p
ON      p.cust_id = 0
        AND p.art_id = e.art_id

Make sure that (cust_id, art_id) (in this order) is a PRIMARY KEY or a UNIQUE INDEX on price.

