Pulling data from identical tables

Databases
1

Hai folks,

we are recruiting candidates for 3 projects.
project a , project b and project c

candidats are put on 3 tables for the projects they were hired

candidates_project_a
candidates_project_b
candidates_project_c

i want to list all the candidats from 3 tables on the screen. we have total 400 candidates.

i have the flowing query

	$query="SELECT candidates_project_a.*, candidates_project_b.*, candidates_project_c.*
            From candidates_project_a, candidates_project_b, candidates_project_c

now this displays 14000+ records while i only have 400 :rolleyes:

what could be wrong?

2

When you join tables together like that every record in the first table is combined with every record in the second table and those entries are then combined with every record from the third table giving you potentially millions of results.

What you need to get the results you are after is to UNION the results of three SELECTs each of which gets the records from only one table.

3

Thanks felgall,
i am new to this UNION.
let me read some examples on how to do this :slight_smile:

4

worked charm felgall!
Thanks so much for your valuable time!!!


	$query="SELECT * FROM candidates_project_a
	             UNION
		     SELECT * FROM candidates_project_b
		     UNION 
		     SELECT * FROM candidates_project_c";
5

Even better would be to create a single projects table that describes all the projects and then a separate candidates table that points back to this projects table. That way you can query just the candidates table with a WHERE clause specifying which projects you want and it allows you application to scale easily. i.e., you don’t need to create a table for a new project anymore.

6

Thanks Scallio, undestood!!