需要帮助狡猾的mySQL查询

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)?

我在这里要求了很多东西——但是也许一些SQL专家可以告诉我如何提取我想要的数据，并为我节省10多个小时的google时间(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.

带有用户ID“0”的“price”DB中的价格是标准价格，如果存在带有art_id和cust_id的条目，则为该文章的客户特定价格。

What I have is cust_id and what I have for output now is just the customer specific prices with SQL:

我所拥有的是cust_id，我现在输出的是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

从cust_id = custID的表事件中获取所有id

2 Get all distinct article ID's on those orders from table event_art

从表event_art中获取所有不同的文章ID

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.

如果没有条目，则使用custID从价格表中输出物品的“id”和“art_name”。

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?

对我来说，这听起来像是多行连接，有点超出了我的SQL知识范围。有人能帮我解决这个问题吗?嗯,什么吗?

Thanks in advance!

提前谢谢!

3 个解决方案

#1

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.

确保(cust_id、art_id)(按此顺序)是主键或唯一的价格索引。

#2

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

必须做一些小的更改，以指示在SQL中使用了哪个表，但是几乎所有的copy&paste(复制粘贴)都非常不错: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

#3

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

#1

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