CODEWITHSUNDEEP
sqlms-access
MGP
MGP

Reputation: 2551

SQL JOIN tables based on values

I'm struggeling with the following:

I have two tables:

table_1:

kalib_name  var_name  y_0  y_1  y_2  ...  y_10

and

table_2:

var_name

I now want to construct a table, where for every kalib_name there are all var_name form table_2, even if the combination kalib_name var_name doesn't exist in table_1 and fill the other columns (y_0 ... y_10) with NULL or 0. I have tried:

SELECT * FROM 
    (SELECT DISTINCT table_2.[var_name] AS variable_name
    FROM table_2
    WHERE table_2.[var_name] IS NOT NULL) AS A
LEFT JOIN table_1
ON (A.variable_name = table_1.[var_name])

But this doesn't seem to work. I have 69 distinct values in table_2 and 101 distinct kalib_names. I would expect to get an table from the length 69*101, mine is however only about 3800 entries long.

Upvotes: 2

Views: 67

Answers (4)

Parfait
Parfait

Reputation: 107632

Consider a combination of a cross join for all combination pairs and then LEFT JOIN to pull in data in existing pairs wrapped in NZ() for NULL to zero handling:

SELECT pairs.kalib_name, pairs.var_name, 
       NZ(table1.y_0) AS y_0,
       NZ(table1.y_1) AS y_1,
       NZ(table1.y_2) AS y_2,
       NZ(table1.y_3) AS y_3,
       ...
       NZ(table1.y_10) AS y_10,
FROM 

  (SELECT t1.kalib_name, t2.var_name
   FROM (select distinct kalib_name from table1
         where kalib_name is not null) AS t1, 
        (select distinct var_name from table2 
         where var_name is not null) AS t2
  ) pairs

LEFT JOIN table1
   ON pairs.[kalib_name] = table1.[kalib_name]
   AND pairs.[var_name] = table1.[var_name]

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269803

Use left join:

select t2.var_name, . . .   -- the other columns you want
from table2 as t2 left join
     table1 as t1
     on t2.var_name = t1.kalib_name;

EDIT:

If you want a cross product, then in MS Access you use a comma (it doesn't support CROSS JOIN:

select t1.var_name, t1.kalib_name
from (select distinct var_name from table_2) as t2,
     (select distinct kalib_name from table_1) as t1;

Upvotes: 0

Meera
Meera

Reputation: 318

Try this to get one entry for each table_1 - table_2 combination with the other columns you want present set to 0:

SELECT table_1.kalib_name, table_2.var_name, 0 AS y_0, 0 AS y_1, ..., 0 AS y_10 FROM table_1, table_2

If you want one entry for each unique combination add DISTINCT:

SELECT DISTINCT table_1.kalib_name, table_2.var_name, 0 AS y_0, 0 AS y_1, ..., 0 AS y_10 FROM table_1, table_2

Upvotes: 0

Erwin Smout
Erwin Smout

Reputation: 18408

That's plain old-style cartesian product :

SELECT kalib_name, table_2.var_name FROM table_1, table_2 ;

Upvotes: 1

Related Questions