SQL multiple join on two tables

Question

I am a novice SQL user.

I have two tables. On has overview information about linear features, e.g. feature type, name, etc., table_1. Each linear feature exists in three spatial zones. The second table has the x,y locations for each linear feature as it crosses from zone to zone, table_2. The x,y is in two columns and the zone crossed into is in a 3rd. The only column that I have to tie the tables to each other is the name. What I would like to do is join the x,y information to table_1 three times. The only column that I have to Once for each zone.

  TABLE_1
    NAME    TYPE
    LINE_1  A
    LINE_2  B

  TABLE_2
    NAME    ZONE    X       Y
    LINE_1  ZONE_1  53.36   48.99
    LINE_2  ZONE_1  4.36    81.38
    LINE_1  ZONE_2  41.11   93.85
    LINE_2  ZONE_2  92.86   5.37
    LINE_1  ZONE_3  3.44    87.41
    LINE_2  ZONE_3  24.45   78.60


  TABLE_OUT
    NAME    TYPE    X_ZONE_1    Y_ZONE_1    X_ZONE_2    Y_ZONE_2    X_ZONE_3    Y_ZONE_3
    LINE_1  A       53.36       48.99       41.11       93.85       3.44        87.41
    LINE_2  B       4.36        81.38       92.86       5.37        24.45       78.60

I can do one zone ok...

SELECT A.NAME, B.X AS X_ZONE_1, B.Y AS Y_ZONE_2 FROM
TABLE_1 A, TABLE_2 B
WHERE A.NAME = B.NAME 
AND B.ZONE = '1'

Please edit your question and include sample data and desired results. — Gordon Linoff
– Gordon Linoff, Commented Jul 5, 2015 at 16:39

APC · Accepted Answer · 2015-07-08 04:31:38Z

As a novice you should learn to play with SQL. The worst that can happen is you get a compilation error ;-)

With a little experimentation you might have found this:

SELECT A.NAME, B1.X AS X_ZONE_1, B1.Y AS Y_ZONE_1 
             , B2.X AS X_ZONE_2, B2.Y AS Y_ZONE_2
             , B3.X AS X_ZONE_3, B3.Y AS Y_ZONE_3 
FROM  TABLE_1 A, TABLE_2 B1
      , TABLE_2 B2
      , TABLE_2 B3 
WHERE A.NAME = B1.NAME 
AND B1.ZONE = '1'
AND A.NAME = B2.NAME 
AND B2.ZONE = '2'
AND A.NAME = B3.NAME 
AND B3.ZONE = '3'

Note that we need those table and column aliases to make this work.

"This mostly works but it is returning too many records."

I cannot comment on your actual SQL without seeing your actual SQL and some representative input data. But in general, too many rows is due to:

missing join expressions
missing filter expressions

The way to debug queries such as you have is to build them incrementally. Start with just TABLE_1; make sure it returns one row. Then join it to TABLE_2. Still get one row? Join TABLE_3. Repeat until you get a result you are not expecting. Building the SQL incrementally means you are less likely to introduce typos, and it's easier to spot them when you do.

Note that the posted example here has no filters for TABLE_1, so it will return at least one row for each row in the driving table. If there is not a one-to-one correspondence between NAME and TYPE you will get multiple results for each value of NAME.

The ANSI 92 join syntax is helpful with complicated queries . It separates the joins and the filters, which many people find more readable:

SELECT A.NAME, B1.X AS X_ZONE_1, B1.Y AS Y_ZONE_1 
             , B2.X AS X_ZONE_2, B2.Y AS Y_ZONE_2
             , B3.X AS X_ZONE_3, B3.Y AS Y_ZONE_3 
FROM  TABLE_1 A
          join TABLE_2 B1 on B1.NAME = A.NAME
          join TABLE_2 B2 on B2.NAME = A.NAME
          join TABLE_2 B3 on B2.NAME = A.NAME
WHERE B1.ZONE = '1'
AND B2.ZONE = '2'
AND B3.ZONE = '3'

This mostly works but it is returning too many records. I do have more then two tables to join, a total of 5 source tables. My sql is making 16 records where I would like only be one.

MT0 · Accepted Answer · 2015-07-05 20:05:41Z

Using only a single join:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TABLE_1 ( NAME, TYPE ) AS
          SELECT 'LINE_1', 'A' FROM DUAL
UNION ALL SELECT 'LINE_2', 'B' FROM DUAL;

CREATE TABLE TABLE_2 (NAME, ZONE, X, Y ) AS
          SELECT 'LINE_1', 'ZONE_1', 53.36, 48.99 FROM DUAL
UNION ALL SELECT 'LINE_2', 'ZONE_1',  4.36, 81.38 FROM DUAL
UNION ALL SELECT 'LINE_1', 'ZONE_2', 41.11, 93.85 FROM DUAL
UNION ALL SELECT 'LINE_2', 'ZONE_2', 92.86,  5.37 FROM DUAL
UNION ALL SELECT 'LINE_1', 'ZONE_3',  3.44, 87.41 FROM DUAL
UNION ALL SELECT 'LINE_2', 'ZONE_3', 24.45, 78.60 FROM DUAL;

Query 1:

SELECT t1.NAME,
       t1.TYPE,
       MIN( CASE ZONE WHEN 'ZONE_1' THEN X END ) AS X_ZONE_1,
       MIN( CASE ZONE WHEN 'ZONE_1' THEN Y END ) AS Y_ZONE_1,
       MIN( CASE ZONE WHEN 'ZONE_2' THEN X END ) AS X_ZONE_2,
       MIN( CASE ZONE WHEN 'ZONE_2' THEN Y END ) AS Y_ZONE_2,
       MIN( CASE ZONE WHEN 'ZONE_3' THEN X END ) AS X_ZONE_3,
       MIN( CASE ZONE WHEN 'ZONE_3' THEN Y END ) AS Y_ZONE_3
FROM   TABLE_1 t1
       INNER JOIN
       TABLE_2 t2
       ON ( t1.NAME = t2.NAME )
GROUP BY
       T1.NAME,
       T1.TYPE

Results:

|   NAME | TYPE | X_ZONE_1 | Y_ZONE_1 | X_ZONE_2 | Y_ZONE_2 | X_ZONE_3 | Y_ZONE_3 |
|--------|------|----------|----------|----------|----------|----------|----------|
| LINE_1 |    A |    53.36 |    48.99 |    41.11 |    93.85 |     3.44 |    87.41 |
| LINE_2 |    B |     4.36 |    81.38 |    92.86 |     5.37 |    24.45 |     78.6 |

Collectives™ on Stack Overflow

SQL multiple join on two tables

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related