Re: Making Matched Records with Primary File in Query an Outer join in SQL

Which, while the statement below works fine, leaves me wondering a few
things.

Question: How does it know which files are being joined, by column name?
Suppose instead of using column names made unique to the table I used more
common names.

select * from pete1
1....+....2....+....
KEY DATA
1 1
1 2
1 3
2 1
2 5
End of data ******

select * from pete2
1....+....2....+....
DATA A2
1 10
2 11
3 12
4 13
End of data ******

select * from pete3
1....+....2....+....
DATA A3
1 20
3 22
4 23
5 24
End of data ******

select * from pete4
1....+....2....+....
DATA A4
2 41
3 42
4 43
5 44
End of data ******

select * from pete5
1....+....2....+....
DATA A5
1 60
2 61
5 64
End of data ******

select key, pete1.data,
pete2.data, a2,
pete3.data, a3,
pete4.data, a4,
pete5.data, a5
from pete1 left outer join pete2 using (data)
left outer join pete3 using (data)
left outer join pete4 using (data)
left outer join pete5 using (data)
where key<1000
order by key
This doesn't work because, while this line is right
from pete1 left outer join pete2 using (data)
this line is not:
left outer join pete3 using (data)
This second line is ambiguous. Actually I can understand that. So, if I
change the statement to:
select key, pete1.data,
pete2.data, a2,
pete3.data, a3,
pete4.data, a4,
pete5.data, a5
from pete1 left outer join pete2 using (data)
left outer join pete3 on pete1.data=pete3.data
left outer join pete4 on pete1.data=pete4.data
left outer join pete5 on pete1.data=pete5.data
where key<1000
order by key

it works fine.

Rob Berendt