• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 270
  • Last Modified:

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.
James Elliott
James Elliott
2 Solutions
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Not necessarily a Join, methinks.

select MyRef, MyLetter, MyPrice
from T2
where MyLetter<>'d'
select MyRef, 'a', MyPrice
from T2
where MyLetter='d'
select MyRef, 'b', MyPrice
from T2
where MyLetter='d'
select MyRef, 'c', MyPrice
from T2
where MyLetter='d'

Open in new window

Scott PletcherSenior DBACommented:
SELECT t1.code, t1.option, t2.price
FROM table1 t1
    t2.code = t1.code AND
    (t1.option = t2.option OR t2.option = 'D')
James ElliottManaging DirectorAuthor Commented:
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now