Select DISTINCT from multiple columns of multiple tables with PHP and MySQL

Question

Using PHP and MySQL I'm trying to get all the distinct values out the number and type columns from all 4 of the tables listed below:

table_1

ID|t1_number|t1_type
1|1|new
2|1|old
3|2|new
4|3|new
5|1|old

table_2

ID|t2_number|t2_type
1|1|future
2|1|new
3|3|past
4|3|new
5|1|new

table_3

ID|t3_number|t3_type
1|1|past
2|1|new
3|1|new
4|1|new
5|1|old

table_4

ID|t4_number|t4_type
1|1|new
2|4|new
3|3|old
4|2|new
5|1|new

The values I want from the above tables would be:

numbers: 1,2,3,4

types: new,old,future,past

Here is what I have so far; but I'm not sure if the SQL is correct or how to format the while loop to get the values out.

$sql = "SELECT DISTINCT table_1.t1_number, table_2.t2_number, table_3.t3_number, table_4.t4_number, table_1.t1_type, table_2.t2_type, table_3.t3_type, table_4.t4_type
FROM table_1 
JOIN table_2
JOIN table_3
JOIN table_4";

$result = @mysql_query($sql, $con) or die(mysql_error());

while($row = mysql_fetch_array($result)) {

    $numbers= $row[?????????];

}

Answer 1

With SQL:

SELECT DISTINCT t1_number AS num FROM t1
    WHERE num NOT IN (SELECT t2_number FROM t2)
    AND num NOT IN (SELECT t3_number FROM t3)
    AND num NOT IN (SELECT t4_number FROM t4)

The same idea will work for the types.

Answer 2

It's not completely clear what you're asking for. Do you want the four distinct numbers, and then the four distinct types?

SELECT t1_number FROM table_1
UNION SELECT t2_number FROM table_2
UNION SELECT t3_number FROM table_3
UNION SELECT t4_number FROM table_4;

SELECT t1_type FROM table_1
UNION SELECT t2_type FROM table_2
UNION SELECT t3_type FROM table_3
UNION SELECT t4_type FROM table_4;

Or do you want each distinct pair of number & type?

SELECT t1_number, t1_type FROM table_1
UNION SELECT t2_number, t2_type FROM table_2
UNION SELECT t3_number, t3_type FROM table_3
UNION SELECT t4_number, t4_type FROM table_4;

The neat thing about UNION is that it implicitly reduces the result to distinct rows. You can preserve duplicates if you use UNION ALL.

Answer 3

Doing this in a single query risks duplicating a value in the output for either the number or type column, in the case that one list has more values than the other. To get a distinct list of values for either column, this needs to be separate queries:

Number

SELECT t1_number AS num
  FROM TABLE_1
UNION
SELECT t2_number
  FROM TABLE_2
UNION
SELECT t3_number
  FROM TABLE_3
UNION
SELECT t4_number
  FROM TABLE_4

Type

SELECT t1_type AS type
  FROM TABLE_1
UNION
SELECT t2_type
  FROM TABLE_2
UNION
SELECT t3_type
  FROM TABLE_3
UNION
SELECT t4_type
  FROM TABLE_4

There's no value to running DISTINCT in this UNION query, because duplicates will be removed, and this deals with only one column.

Answer 4

I think this is what you want:

select distinct number_value, type_value from (
  select t1_number as number_value, t1_type as type_value from table_1
union
  select t2_number as number_value, t2_type as type_value from table_2
union
  select t3_number as number_value, t3_type as type_value from table_3
union
  select t4_number as number_value, t4_type as type_value from table_4
)