oracle - SQL multiple join on two tables -

i novice sql user.

i have 2 tables. on has overview information linear features, e.g. feature type, name, etc., table_1. each linear feature exists in 3 spatial zones. second table has x,y locations each linear feature crosses zone zone, table_2. x,y in 2 columns , zone crossed in 3rd. column have tie tables each other name. join x,y information table_1 3 times. column have once each zone.

  table_1     name    type     line_1      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        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 1 zone ok...

select a.name, b.x x_zone_1, b.y y_zone_2 table_1 a, table_2 b a.name = b.name  , b.zone = '1' 

as novice should learn play sql. worst can happen compilation error ;-)

with little experimentation might have found this:

select a.name, b1.x x_zone_1, b1.y y_zone_1               , b2.x x_zone_2, b2.y y_zone_2              , b3.x x_zone_3, b3.y y_zone_3   table_1 a, table_2 b1       , table_2 b2       , table_2 b3  a.name = b1.name  , b1.zone = '1' , a.name = b2.name  , b2.zone = '2' , a.name = b3.name  , b3.zone = '3' 

note need table , column aliases make work.

---

"this works returning many records."

i cannot comment on actual sql without seeing actual sql , representative input data. in general, many rows due to:

• missing join expressions
• missing filter expressions

the way debug queries such have build them incrementally. start table_1; make sure returns 1 row. join table_2. still 1 row? join table_3. repeat until result not expecting. building sql incrementally means less introduce typos, , it's easier spot them when do.

note posted example here has no filters table_1, return @ least 1 row each row in driving table. if there not one-to-one correspondence between name , type multiple results each value of name.

the ansi 92 join syntax helpful complicated queries . separates joins , filters, many people find more readable:

select a.name, b1.x x_zone_1, b1.y y_zone_1               , b2.x x_zone_2, b2.y y_zone_2              , b3.x x_zone_3, b3.y y_zone_3   table_1           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 b1.zone = '1' , b2.zone = '2' , b3.zone = '3'