StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMySQL - How can I use left join to get exactly one row from a joined table in the on clause?
text
Body
copied!<p>I hope the question is clear enough.</p> <p>Here's my query:</p> <pre><code>SELECT 'Total Complete' AS name, count(c.id) AS amnt FROM lanes AS c LEFT JOIN leads AS l ON ((c.carrier_mcn = l.authority_number AND l.authority_type = 'mc') OR c.carrier_mcn = l.mcn OR c.carrier_mcn = CONCAT('MC', l.authority_number)) AND c.carrier_mcn <> 0 LEFT JOIN lanes_emails AS cem1 ON cem1.cid = c.id AND cem1.deleted = 0 LEFT JOIN lanes_emails AS cem2 ON cem1.cid = cem2.cid AND cem2.deleted = 0 AND cem2.id < cem1.id LEFT JOIN lanes_equipment AS ceq1 ON ceq1.cid = c.id AND ceq1.deleted = 0 LEFT JOIN lanes_equipment AS ceq2 ON ceq1.cid = ceq2.cid AND ceq1.deleted = 0 AND ceq2.id < ceq1.id LEFT JOIN lanes_service_area AS csa1 ON csa1.cid = c.id AND AND flag = 2 csa1.deleted = 0 LEFT JOIN lanes_service_area AS csa2 ON csa1.cid = csa2.cid AND AND flag = 2 csa1.deleted = 0 AND csa2.id < csa1.id WHERE c.carrier_mcn IS NOT NULL and c.carrier_mcn <> '' AND c.broker_mcn IS NOT NULL and c.broker_mcn <> '' AND c.fax IS NOT NULL and c.fax <> '' AND cem2.id IS NOT NULL AND ceq2.id IS NOT NULL AND csa2.id IS NOT NULL </code></pre> <p>Now what I'm trying to do here is get the total count of rows from "lanes" where it has at least one email, equipment, service area, mc number, or fax number. There are two MC Numbers, but they're both on the same table; the main issue is that there can be unlimited emails, equipment, and service areas.</p> <p>In this case, I starting writing out an article about this issue and discovered another similar problem:</p> <p><a href="https://stackoverflow.com/questions/11131237/mysql-can-i-do-a-left-join-and-pull-only-one-row-from-the-join-table">MySQL: Can I do a left join and pull only one row from the join table?</a></p> <p>So I tried it out and implemented it into my query. Unfortunately, it didn't work the way I had planned.</p> <p>It would work fine if I had more than one row; it would get the correct count, ignoring the fact that I had multiple emails or whatnot, but it would NOT work if there was only one. Instead, it returned no results.</p> <p>No, I cannot use GROUP BY to get the results I need; I've tried GROUP BY, I love using it when returning full result lists, but if I'm trying to return counts GROUP BY repeatedly messes me up.</p> <p>Is there any way to force a join in mysql to return just one row without using a subquery? I only need to know if there is one item from any of these other tables, I don't need to return everything, and I don't know of a way to program it to work through the WHERE clause.</p> <p>I need this query for a bar chart. I've actually got several of these queries; this one is the one that requires all results, but I have others for no requirements, at least one email, at least one mc number, etc., but if I can get this working I can get the rest working.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload