Link to home
Start Free TrialLog in
Avatar of James Elliott
James ElliottFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL conditional join

Hi All,

I have a list of codes and options. A code can either have the option a, b, or c (for example):

A00001    a
A00001    b
A00002    a
A00002    c
A00003    b


In another table, I have these codes and options, mapped to prices

A00001    a    £1
A00001    b    £2
A00002    a    £3
A00002    c    £4
A00003    b   £5

However, in the second list, if all prices for all options are the same, then the code will have just one line, with the option of 'd' indicating that all options attract the same price.

A00004    d  £6

So my question is how do a write a join so that I return the correct price next to every code and option in the first list?

Thanks in advance.
SOLUTION
Avatar of Phillip Burton
Phillip Burton

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of James Elliott

ASKER

Thanks. I went with the second solution but have awarded points to the first as it also taught me an approach with Union that I hadn't previously considered.

Rgds