1

I'm having a bit of trouble figuring out how to do this -- I need to select all rows from table t1, join table t2 -- but include a null row from t2 for each record in t1 (even if rows match in t2).

For my case, I actually need to do this with four tables:

Table t1 has t1_id, name

Table t2 has t2_id, name

Table t3 has t3_id, name

Table t4 has t4_id, name

I need to join them all to get something like:

-----------------------------
t1_id | t2_id | t3_id | t4_id
-----------------------------
1     | NULL  | NULL  | NULL
-----------------------------
1     | 1     | NULL  | NULL
-----------------------------
1     | 1     | 1     | NULL
-----------------------------
1     | 1     | 1     | 1
-----------------------------

This would be the final result of a join of the four tables, each having one row with an tx_id of 1.

  • Please elaborate, what do you want to accomplish with this join? Aggregates or subtotals of some kind? – Daniel Hutmacher Aug 19 '14 at 19:17
1

All INNER JOINs and UNIONs maybe

PROPOSED QUERY

select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
union select t1.id,t2.id,NULL,NULL from t1
    inner join t2 using (id)
union select t1.id,t2.id,t3.id,NULL from t1
    inner join t2 using (id)
    inner join t3 using (id)
union select t1.id,t2.id,t3.id,t4.id from t1
    inner join t2 using (id)
    inner join t3 using (id)
    inner join t4 using (id)
;

SAMPLE DATA

use test
drop table if exists t1;
drop table if exists t2;
drop table if exists t3;
drop table if exists t4;
create table t1
(
    id int not null,
    name varchar(20),
    primary key (id)
); 
insert into t1 values (1,'name1');
create table t2 like t1; insert into t2 values (1,'name2');
create table t3 like t1; insert into t3 values (1,'name3');
create table t4 like t1; insert into t4 values (1,'name4');

PROPOSED QUERY EXECUTED

mysql> select id t1_id,NULL t2_id,NULL t3_id,NULL t4_id from t1
    -> union select t1.id,t2.id,NULL,NULL from t1
    ->     inner join t2 using (id)
    -> union select t1.id,t2.id,t3.id,NULL from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    -> union select t1.id,t2.id,t3.id,t4.id from t1
    ->     inner join t2 using (id)
    ->     inner join t3 using (id)
    ->     inner join t4 using (id)
    -> ;
+-------+-------+-------+-------+
| t1_id | t2_id | t3_id | t4_id |
+-------+-------+-------+-------+
|     1 |  NULL |  NULL |  NULL |
|     1 |     1 |  NULL |  NULL |
|     1 |     1 |     1 |  NULL |
|     1 |     1 |     1 |     1 |
+-------+-------+-------+-------+
4 rows in set (0.00 sec)

mysql>

All the columns have id as a common name. If you want, I can adjust my answer with the id names

  • I marked this as right mainly for the use of the unions + null columns. I was able to get mine to work as expected with LEFT joins. – Beshoy Girgis Aug 19 '14 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.