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!

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.

Had to make some small changes to indicate which table was used where in the SQL, but pretty much copy&paste so not bad at all :P

SELECT price.art_id, price.price
FROM   price
WHERE  cust_id =114
UNION ALL
SELECT e.art_id, p.price
FROM (

SELECT DISTINCT art_id
FROM event e
JOIN event_art ea ON ea.event_id = e.id
WHERE e.cust_id =114
AND ea.art_id NOT
IN (

SELECT price.art_id
FROM price
WHERE cust_id =114
)
)e
JOIN price p ON p.cust_id =0
AND p.art_id = e.art_id

SELECT DISTINCT
  a.id,
  a.art_name,
  COALESCE(p.price, p0.price) AS price
FROM event e
  INNER JOIN event_art ea ON e.id = ea.event_id
  INNER JOIN art a ON ea
  LEFT JOIN price p ON p.art_id = a.id AND p.cust_id = e.cust_id
  LEFT JOIN price p ON p.art_id = a.id AND p.cust_id = 0