GETTING VALUES IN DATASET FROM TWO TABLES DEPENDING ON VALUE

Question

GETTING VALUES IN DATASET FROM TWO TABLES DEPENDING ON VALUE

Ronvy

Mr or Mrs. 500

Points: 597
More actions
February 8, 2006 at 1:50 am

#167432

I have a table called ORDER.
There is a field ItemID in ORDER table.
The value in ItemID can be any value that is also (primary keys) in tables STANDARD or CUSTOM.
There is ItemName field in both the tables corresponding to the ItemID.
So my issue is I need to join ORDER table with STANDARD or CUSTOM depending on the value in ItemID in ORDER table and get the ItemName in the dataset.
ORDER TABLE
-----------
OrderID      ItemID             MfgID    ....
STANDARD TABLE
----------------
ItemID       ItemName
CUSTOM TABLE
----------------
ItemID       ItemName
Can anyone please help me with a solution, I'd be grateful.
Thanks,
Ron.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

yellowleonard SSC-Addicted Points: 402 More actions · Answer 1

Hi Ron,

How do you know that the ItemID's in Standard and Order are different from each other? Is there an obvious way of telling the difference or are they just integers?

Gareth

Ronvy Mr or Mrs. 500 Points: 597 More actions · Answer 2

Hi Gareth, thanks for replying.

The values of ItemID will be different because i've set a different range for them in both the tables.

STANDARD - 1000 to 3000.

CUSTOM - 5000 to 6000.

I did this because of the complexity that could arise had i not kept them different.

So eventually when i write the query, my dataset should contain all the fields from ORDER TABLE and also depending on the ItemID in ORDER TABLE, it should get the correct ItemName from STANDARD or CUSTOM.

ie; if the ItemID in ORDER TABLE is between 1000 and 3000, then it would get ItemName from STANDARD TABLE else if between 5000 and 6000, then from CUSTOM TABLE.

(But is ther a possiblity of getting the ItemName if both the tables have same value? Just a thought)

Thanks once again,

yellowleonard SSC-Addicted Points: 402 More actions · Answer 3

At first I was thinking maybe a case statement, but now I'm leaning towards using a View e.g.

Create View vw_Items As

Select

*

From

Standard, Custom

Then you can:

select * from vw_Items where ItemID = X

Hope that made sense.

yellowleonard SSC-Addicted Points: 402 More actions · Answer 4

Sorry you wanted to join Orders so that would actually be:

select

*

from

vw_Items vi Join Orders od On vi.ItemID = od.ItemID

to return you everything from orders and the other two tables.

Liliana Sorrentino SSC-Addicted Points: 400 More actions · Answer 5

Hi,

Try this, so your code is independent from values. I suggest don't repeat ItemID in both tables, but if you do, this query will return Standard value.

SELECT O.OrderID,

O.ItemID,

ItemName = ISNULL(S.ItemName, C.ItemName)

FROM [Order] O

LEFT JOIN Standard S ON O.ItemID = S.ItemID

LEFT JOIN Custom C ON O.ItemID = C.ItemID

Liliana.

yellowleonard SSC-Addicted Points: 402 More actions · Answer 6

Liliana's is a good solution too as long as Ron is sure he's never going to duplicate ID's between the Standard and Custom tables. If he does, then Standard table's ItemName will always be favoured in the case of duplicates.

I should have used a left join in my example too. D'oh.

Ronvy Mr or Mrs. 500 Points: 597 More actions · Answer 7

Hi Liliana & Gareth,

Finally i got the required query,

Yes Gareth, you were right, i had the problem of duplicate values,

But i used distict for that and solved it.

SELECT distinct ItemName = ISNULL(S.ItemName, C.ItemName)

FROM [Order] O

LEFT JOIN Standard S ON O.ItemID = S.ItemID

LEFT JOIN Custom C ON O.ItemID = C.ItemID

Blimey !! , That was wonderful Liliana,

Thanks a lot for ur time & effort both of ya. I really appreciate it.