Solved

SQL conditional join

Posted on 2014-09-30
3
260 Views
Last Modified: 2014-10-01
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.
0
Comment
Question by:James Elliott
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 200 total points
ID: 40352419
Not necessarily a Join, methinks.

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

Open in new window

0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 40352481
SELECT t1.code, t1.option, t2.price
FROM table1 t1
LEFT OUTER JOIN table2 t2 ON
    t2.code = t1.code AND
    (t1.option = t2.option OR t2.option = 'D')
0
 
LVL 12

Author Closing Comment

by:James Elliott
ID: 40354002
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
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question