CODEWITHSUNDEEP
mysqlsql
Joseph
Joseph

Reputation: 859

How to obtain data that is in at least one table of several related in MYSQL

I need help with a problem I have about SQL.

I have three tables, Table1, Table2, Table3 all with an "email" field with which they relate to each other.

On the other hand, I have an array with a list of emails that I have to check if they are in one of those tables. These emails also have them saved in a string separated with commas and with their respective quotes to use it in an IN (....):

$emails = ("'[email protected]', '[email protected]', ....");

SELECT * 
FROM Table t1 
INNER JOIN Table2 t2 ON t1.email = t2.email 
INNER JOIN Table3 t3 ON t1.email = t3.email 
WHERE (t1.email IN ($ emails)) OR (t2.email IN ($ emails) ) OR (t3.email IN ($ emails));

With the previous example, I realized that when doing an INNER JOIN I was matching the tables by the email field which did not have the value in all the tables. It may be the case that an email is not in some table but in another yes, so I need an SQL query so that I get the emails that are in ANY of the 3 tables.

I have been told that I made a temporary table of the emails array and then doing LEFT JOIN between the tables I could get it, but I do not know how to do it.

Could you give me an idea of ​​how to solve this problem?

Thanks in advance.

Upvotes: 0

Views: 44

Answers (2)

Barmar
Barmar

Reputation: 782775

You need to use UNION, not JOIN.

SELECT email, "Table1" AS whichTable
FROM Table1
WHERE email in ($email)
UNION
SELECT email, "Table2" AS whichTable
FROM Table2
WHERE email in ($email)
UNION
SELECT email, "Table3" AS whichTable
FROM Table3
WHERE email in ($email)

Using SELECT * in all the queries assumes that all the tables have similar columns in the same order. If not, you'll need to list the columns explicitly in each query.

Upvotes: 1

gahooa
gahooa

Reputation: 137592

Here is a straightforward way that should return a single Boolean value. EXISTS is useful because it (should) ignore looking at any additional rows as soon as it finds one.

SELECT 
   EXISTS (SELECT * FROM table1 WHERE email IN (...))
   OR
   EXISTS (SELECT * FROM table2 WHERE email IN (...))
   OR
   EXISTS (SELECT * FROM table3 WHERE email IN (...))

Upvotes: 0

Related Questions