CODEWITHSUNDEEP
sqlpostgresql
user3206440
user3206440

Reputation: 5059

joining wide tables (10s of unique cols)

I have multiples tables which need to be joined on multiple common attributes such the different attributes can be shown in a single table.

table1

+--------+---------+-------+
|  make  |  model  | r_yr  |
+--------+---------+-------+
| toyota | corolla |  1999 |
| toyota |  camry  |  2002 |
| toyota |  qualis |  2004 |
| toyota |  rav4   |  2006 |
+--------+---------+-------+

table2

+--------+---------+--------+
|  make  |  model  |  kms   |
+--------+---------+--------+
| toyota | corolla |  25000 |
| toyota |  camry  |  50000 |
+--------+---------+--------+

table4

+--------+---------+---------+
|  make  |  model  | mileage |
+--------+---------+---------+
| toyota | corolla |      20 |
| toyota |  qualis |      25 |
+--------+---------+---------+

table5

+--------+----------+-------+
|  make  |  model   | colr  |
+--------+----------+-------+
| toyota |  camry   | blue  |
| toyota |  rav4    | green |
+--------+----------+-------+

I'm doing the following to join the results

select a.make, a.model,a.r_yr,b.kms,c.mileage,d.colr
    from table1 as a
    left join table2 as b
    on b.make=a.make and b.model=a.model and b.r_yr=a.r_yr
    left join table3 as c
    on c.make=a.make and c.model=a.model and c.r_yr=a.r_yr
    left join table4 as d
    on d.make=a.make and d.model=a.model and d.r_yr=a.r_yr

This gives a table like below

+--------+---------+-------+-------+----------+--------+
|  make  |  model  | r_yr  |  kms  |  mileage |  colr  |
+--------+---------+-------+-------+----------+--------+
| toyota | corolla |  1999 | 25000 |       20 |        |
| toyota |  camry  |  2002 | 50000 |          |  blue  |
| toyota |  qualis |  2004 |       |       25 |        |
| toyota |  rav4   |  2006 |       |          | green  |
+--------+---------+-------+-------+----------+--------+

However the issue I have is that, for the real data set I'm working with, there are 5 common cols per table and around 20-40 unique attributes per table requiring to specify 20-40 col names in the query in the form of b.kms, ....,c.mileage, ......,d.colr,..... Is there a work around to not having to specify those unique columns by specifying all except the common cols or other ways ?

Upvotes: 2

Views: 35

Answers (1)

krokodilko
krokodilko

Reputation: 36117

You cannot do something like SELECT all except x,y,z ...
But you can simplify this query using USING clause instead of JOIN ... ON

Demo: http://sqlfiddle.com/#!17/fa97a/6

select *
from table1 as a
left join table2 as b
USING (make, model)
left join table3 as c
USING (make, model)
left join table4 as d
USING (make, model)

|   make |   model | r_yr |    kms | mileage |   colr |
|--------|---------|------|--------|---------|--------|
| toyota |   camry | 2002 |  50000 |  (null) |   blue |
| toyota | corolla | 1999 |  25000 |      20 | (null) |
| toyota |  qualis | 2004 | (null) |      25 | (null) |
| toyota |    rav4 | 2006 | (null) |  (null) |  green |

Note: In the above example I am using only two common columns (make, model) since in your example r_yr is not a common column because it is only in table1

Upvotes: 4

Related Questions