MySQL从两个具有公共密钥的表中选择不同的值

I am making a query that is using 3 table. the quotes table has a key called quote_id which is stored in the quote_items table. There will only ever be one instance of quote_id in the quotes table but in the quote_items table it may occur several times. I want to join the two together, but filter out any instance where quote_id occurs more than once.

我正在进行使用3表的查询。引号表有一个名为quote_id的键,它存储在quote_items表中。引号表中只有一个quote_id实例,但在quote_items表中可能会多次出现。我想将两者结合在一起,但过滤掉不止一次出现quote_id的任何实例。

SELECT *  FROM quotes 
INNER JOIN quote_items ON 
quotes.quote_id IN (SELECT DISTINCT quote_id FROM quote_items)
INNER JOIN accounts ON 
quotes.account_id = accounts.account_id AND quotes.status = 0 
WHERE primary_sales_id = 2 AND quotes.quote_id = quote_items.quote_id
ORDER BY quotes.date_submitted DESC

You can ignore the account_id part. Basically my line 3 should be different, which I know, but how to go about this I'm not sure.

您可以忽略account_id部分。基本上我的第3行应该是不同的,我知道,但是如何解决这个问题我不确定。

If I had quotes looking like this

如果我的报价看起来像这样

quote_id | quote_name
1        | quote1
2        | qute2

and quote_items looking like this

和quote_items看起来像这样

quote_id | item_name
1        | boards
2        | nails
2        | blocks

I should get back

我应该回来

quote_id | quote_name | item_name
1        | quote1     | boards
2        | quote2     | nails

1 个解决方案

#1

You may use:

你可以使用:

SELECT DISTINCT quotes.quote_id, quote_name, item_name
FROM quotes, quote_items 
WHERE quotes.quote_id = quote_items.quote_id
group by (quote_id );