Page 1 of 1

Intersecting 3 tables

Posted: Tue Jul 24, 2007 5:40 pm
by emmbec
Hi everyone, I've been working hours trying to get the following query done without any success :( What I'm trying to do is sort of intersecting three tables in MySQL, here is an example of the data:

Code: Select all

Table1	     Table2     Table3     Expected Result

Josh	     Jack	    Andrew	    Josh
Jack	     Josh	    Kevin	    Jack
Mary	     Sandy	    Peter		    Peter
Peter	                Roger      Roger
Roger                   Josh       Sandy
Martha                  Sandy
As you can see the main idea is, if there is one repeated name in either table I have to show it. And that is just the easy part, I still have the problem where I can have two or more MySQL Tables, but lets just concentrate on this basic example right now, I'll worry about constructing the query with PHP code later on :P

Thanx in advance, I appreciate your help!

Posted: Tue Jul 24, 2007 5:57 pm
by feyd
Sounds like a job for a UNION, GROUP BY and COUNT() in the where clause.

Posted: Tue Jul 24, 2007 6:13 pm
by programmingjeff
Your query might look something like:

Code: Select all

select distinct table1.name from table1 inner join table2 on table1.name=table2.name
union select distinct table2.name from table2 inner join table3 on table2.name=table3.name
union select distinct table3.name from table3 inner join table1 on table3.name=table1.name
group by name order by name

Posted: Tue Jul 24, 2007 6:24 pm
by emmbec
Thanx man!!! You saved my day!