CODEWITHSUNDEEP
mysqlsql
Henrique Van Klaveren
Henrique Van Klaveren

Reputation: 1760

Check values exist and not exist between 2 tables

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.

Upvotes: 2

Views: 127

Answers (4)

z m
z m

Reputation: 1513

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.

Upvotes: 1

Blag
Blag

Reputation: 5894

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 ?

Upvotes: 2

CharlieGirl
CharlieGirl

Reputation: 46

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

Upvotes: 1

user7656032
user7656032

Reputation: 83

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

Upvotes: 1

Related Questions