CODEWITHSUNDEEP
sqlsql-server
lazaro-santin
lazaro-santin

Reputation: 11

get existing and non existing combinations between two tables through a relationship table

I need help with this.

I have 3 tables (FROMs, TOs and DISTANCEs):

FROMs(Id, Name)
1 - London
2 - Beijing
3 - Athens

TOs(Id, Name)
1 - New York
2 - Madrid
3 - Paris

DISTANCEs(Id,FROMid,TOid,km,obs)
1 - 2 - 3 - 400 - distance between Beij. and Paris

I need a query returning this:

1    - 2 - 3 - 400 - distance between Beij. and Paris
NULL - 1 - 1 - NULL - NULL

and so on for all non-exist combinations.

Thanks.

Upvotes: 0

Views: 151

Answers (1)

Void Ray
Void Ray

Reputation: 10199

Something like this should do:

Here is a link to SQLFiddle

select a.id, b.fromId, b.toId, a.km, a.obs from distances a
right join(
select a.id as fromId, 
       a.name as fromName,
       b.id as toId,
       b.name as toName
from froms a
join tos b
on 1=1) b
on a.fromId = b.fromId
   and a.toId = b.toId

Upvotes: 3

Related Questions