Check values exist and not exist between 2 tables

Question

I have 3 tables, one of accounts, one of friends and another of consumers. Something like this:

table_accounts

id | account_name

table_friends

id | account_id | people_id

table_consumers

id | account_id | people_id

I need to cross the following information:

Which consumer_id coexist in both tables, something simple like this:

SELECT 
    *  
FROM
    table_friends,
    table_consumers
WHERE
    table_friend.account_id = 12345
    AND table_friend.account_id = table_consumers.account_id
GROUP BY table_friend.people_id

this query is very slow

Well, I now need to get what are the consumer_id's friends table, which are NOT in the consumers table. And in a third moment, find out which consumer_id does NOT exist in the friends table. But I think it's the same thing ...

My doubt is about logic, I can not think how to cross this information.

I don't understand you DB schema, and your last § is not clear about what you want, please edit your post with the fields name you want and the relation between table — Blag, Apr 11 '17 at 22:01

score 2 · Accepted Answer · edited May 23 '17 at 12:26

2

This is probably more or less what you try to do : (and take a look at Subqueries with EXISTS vs IN - MySQL )

SELECT *
FROM table_friends
WHERE
    NOT EXISTS (
        SELECT *
        FROM table_consumers
        WHERE table_consumers.people_id = table_friends.people_id
        )

BTW, you say "this query is very slow" how many row you query ? what is "slow" ? do you have some index where you need them ?

edited May 23 '17 at 12:26

Community

1
1

answered Apr 11 '17 at 22:10

Blag

5,818
2
22
45

Your example helped me a lot as well, as in using NOT IN, the example of the friend both worked correctly and served what I needed. Thank you very much for your availability – Henrique Van Klaveren Apr 11 '17 at 23:08

score 1 · Answer 2 · answered Apr 11 '17 at 22:06

1

Could you do something like this:

Select a.account_name
, a.id
, case when f.id is null then 0 else 1 end isFriend
, case when c.id is null then 0 else 1 end isConsumer
from table_accounts a
left join table_friends f on a.id = f.account_id
left join table_consumers c on a.id = c.account_id

answered Apr 11 '17 at 22:06

user7656032

83
5

this is a very interesting, because return only 1 or 0, but this work fine too. Thank you for you time =) – Henrique Van Klaveren Apr 11 '17 at 23:13

score 1 · Answer 3 · edited May 23 '17 at 10:30

1

If I understand your question correctly you can use NOT IN to find the exceptions for each table. Something like this:

SELECT id  
FROM table_consumers  
WHERE account_id  
NOT IN 
  (SELECT account_id 
   FROM table_friends)

You can do the same thing with the table names reversed to find out which friends are not in consumers. If you were wanting to include more than one table in the query, you may want to check out using UNION or UNION ALL as well. See: UNION ALL and NOT IN together

edited May 23 '17 at 10:30

Community

1
1

answered Apr 11 '17 at 22:10

CharlieGirl

46
5

I'll bring this on the table http://stackoverflow.com/questions/14190788/subqueries-with-exists-vs-in-mysql as it may be a good thing to read – Blag Apr 11 '17 at 22:15
1

Great addition Blag! If the table_consumers or table_friends could either return null results - the use of NOT IN could cause a major slow-down - you do want to review EXISTS as Blag suggests. – CharlieGirl Apr 11 '17 at 22:20
Very good, this work fine and very fast. my tables have more then 2M records each, but the slow is because a little error in query :) – Henrique Van Klaveren Apr 11 '17 at 23:03

score 1 · Answer 4 · answered Apr 11 '17 at 22:49

Looks like you already got the answer on how to compose your query, but you should think about the redesign of your schema. If it's not too late.

Both table_friends, and table_consumers represent people. The only difference is what type/kind of people. You don't want to add a new table every time you need to add a new attribute to people. What you need is:

table_accounts
table_people
table_people_type
table_people_type_mapping

The last one being a mapping table between table_people and table_people_type. In table_people_type you could have friends and consumers for now, but you could also add different types later on without schema change. And your queries would be more intuitive.

Again, that is in case if schema change is still an option for you.

I just tried to simplify the system tables so as not to get too confused, the idea of less complicated possible :) Thanks for your feedback. — Henrique Van Klaveren, Apr 11 '17 at 23:27

Check values exist and not exist between 2 tables

4 Answers4