Intersecting 3 tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Intersecting 3 tables

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Sounds like a job for a UNION, GROUP BY and COUNT() in the where clause.
programmingjeff
Forum Commoner
Posts: 26
Joined: Fri Jan 05, 2007 10:56 am

Post 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
User avatar
emmbec
Forum Contributor
Posts: 112
Joined: Thu Sep 21, 2006 12:19 pm
Location: Queretaro, Mexico

Post by emmbec »

Thanx man!!! You saved my day!
Post Reply