SELECT all contacts in table that do NOT have "X"

Question

I am trying to select CONTACTS in my MySQL table, that does NOT have any associated information in the INVESTMENTS table.

I have a query that is something like this, but it's not pulling all my contacts properly.

TL;DR Select ALL contacts, then remove all contacts that have investments

SELECT
    *
FROM
    contacts
INNER JOIN investments ON investments.contactId = contacts.contactId
WHERE
    contacts.userId = '1'
    AND contacts.trash IS NULL
    AND NOT EXISTS ( SELECT * FROM investments WHERE investments.company IS NULL )
GROUP BY
    contacts.contactId

Any help would be greatly appreciated.

Here check out this fiddle, I was going to add an answer but well they closed it. https://www.db-fiddle.com/f/nucna7J1LTX1gMVwiuw1Mf/3 — ArtisticPhoenix, Nov 02 '17 at 00:47
Sure, Like I said I would have put an answer but it was closed, see you around! — ArtisticPhoenix, Nov 02 '17 at 06:03

Aganju · Accepted Answer · 2017-11-02T00:34:19.887

3

An inner join requires a matching entry in the joined table. What you need to find non-matching entries is an outer join, and a simple where condition on a joined table’s field being null.

Outer Joins make up a ‘dummy record’ with all NULL values for the joined table where no match can be found.. for example, if you have table 1 with a, b, c in the only column, and you outer join with table 2 which contains a-1, b-2, you would get a match for the first two entries of table 1, and no match -> dummy record for the 3rd value: a-a-1, b-b-2, c-NULL-NULL. Now you can control what of the result you want to see by adding where conditions, like where table2-col is null, and only the third match remains - c-NULL-NULL.

Note that their are left outer joins and right outer joins, depending if you want the dummies created for unmatched entries of the left or the right table. Sql allows to drop the word outer, so it often reads left join and right join

edited Nov 02 '17 at 00:34

answered Nov 02 '17 at 00:06

Aganju

6,295
1
12
23

Possible to clarify a little? I'm not entirely familiar with OUTER Joins. – Justin Nov 02 '17 at 00:08
1

https://stackoverflow.com/a/38578/6521550 Inner Join vs outer Join – Anup Kumar Gupta Nov 02 '17 at 00:12
1

You can drop the `inner` and just use `JOIN`. thought I should mention that. – ArtisticPhoenix Nov 02 '17 at 00:26
Thanks Aganju, yes that makes sense now. Frustrating how I was using something simple and just a brain fart i suppose not thinking about the outer join. Was thinking totally wrong initially. Appreciate the help on this! Thanks.! – Justin Nov 02 '17 at 00:29
I don't see much in the way of actual SQL, nice explanation though. – ArtisticPhoenix Nov 02 '17 at 00:42

score 1 · Answer 2 · answered Nov 02 '17 at 00:12

1

You can do that by using a LEFT JOIN and checking if the company IS NULL. By using a LEFT JOIN instead of an INNER JOIN you still get the contacts that don't have anything in the investments table.

SELECT
    *
FROM
    contacts
LEFT JOIN investments ON investments.contactId = contacts.contactId
WHERE
    contacts.userId = '1'
    AND contacts.trash IS NULL
    AND investments.company IS NULL
GROUP BY
    contacts.contactId

answered Nov 02 '17 at 00:12

imtheman

4,713
1
30
30

Yeah, frustrating mistake, i was thinking entirely different. The LEFT join makes a world of a difference obviously. So this makes sense. Same as Aganju mentioned. Thanks for the help! – Justin Nov 02 '17 at 00:28

score 1 · Answer 3 · answered Nov 02 '17 at 00:26

1

SELECT * FROM contacts c

LEFT JOIN investments i ON i.contactId = c.contactId

WHERE c.userId = '1' AND c.trash IS NULL AND i.company IS NOT NULL

GROUP BY c.contactId

answered Nov 02 '17 at 00:26

honey

53
2

SELECT all contacts in table that do NOT have "X"

3 Answers3