mysql - 括号内选择不为空

Question

我不太确定该怎么做:

SELECT contacts.first_name,
       contacts.last_name,
       (
            SELECT quotes.id
            FROM quotes 
            LEFT JOIN quotes_contacts ON quotes.id = quotes_contacts.quote_id
            WHERE quotes.id IS NOT NULL
            AND quotes_contacts.contact_id = contacts.id
            ORDER BY quotes.date_entered
            LIMIT 1
        ) AS 'first_quote'
FROM
contacts

但一些作为 first_quote 返回的值正在作为 null 返回。我怎样才能阻止它？

Answer 1

不使用子查询，而是对子查询使用 INNER JOIN。这将消除 NULL。

SELECT contacts.first_name,
       contacts.last_name,
       quote_id AS first_quote
FROM 
  contacts 
  INNER JOIN (
    SELECT quotes.id, quotes_contacts.contact_id
    FROM 
     quotes 
     LEFT JOIN quotes_contacts ON quotes.id = quotes_contacts.quote_id
     JOIN (
       SELECT quotes_contacts.contact_id, MIN(quotes.date_entered) AS date_entered
       FROM 
             quotes 
         JOIN quotes_contacts ON quotes.id = quotes_contacts.quote_id
         GROUP BY contact_id
     ) minquote ON quotes.date_entered = minquote.date_entered AND qc.contact_id = minquote.contact_id
   ) firstquote ON contacts.id = firstquote.contact_id 

如果您确定 quote.id 是递增的，并且每个 的最低 quote.id contact_id 也有最早的 date_entered。在这种情况下，您只需执行 MIN(quote.id)

SELECT contacts.first_name,
       contacts.last_name,
       quote_id AS first_quote
FROM 
  contacts 
  INNER JOIN (
    SELECT MIN(quotes.id) AS quote_id, quotes_contacts.contact_id
    FROM quotes
    LEFT JOIN  quotes_contacts ON quotes.id = quotes_contacts.quote_id
    GROUP BY contact_id
  )minq ON contacts.id = minq.contact_id