Query from one table based on another table...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query from one table based on another table...

Author

Topic

prosit
Starting Member

2 Posts

Posted - 2011-12-13 : 23:14:29

Hi,

I have two tables, one is Provider, the other is Cases.

I need to take two values from provider, but only if the value also exists in Cases.

Tables look like this (have more fields, but these are the needed ones)

Provider:

iProvider iProvType
123 BANK1
321 BANK1
321 BANK2
222 BANK1
111 BANK11
333 BANK4

CASES:

iProvider
123
321
222
232
121

Basically I need to get the iProvider and type from Provider table where the iProvider exists in the Cases table.

Now of course there's a twist.

I only need BANK1 and BANK2, but I also need to return Chase where the value is BANK1 and WellsFargo where its BANK2 and the fields needs to be concatenated into one.

The result I need is:

123 - CHASE
321 - CHASE
321 - WELLSFARGO
322 - CHASE

I appreciate any help...

tnx
~j

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 00:04:41

you should be using mapping table for converting BANK1 to CHASE and BANK2 to WELLSFARGO. and assuming its name is mapping (you need to create it first), query will be like


SELECT CAST(p.iProvider AS varchar(10)) + ' - ' + m.MappedValue
FROM Provider p
INNER JOIN Mapping m
ON m.ProvType = p.iProvType
WHERE EXISTS (SELECT 1 FROM CASES WHERE iProvider = p.iProvider)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

prosit
Starting Member

2 Posts

Posted - 2011-12-14 : 15:31:39

Hi,

Well i am unable to create a new table, but a temp table did the trick, thanks a bunch

~j

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-14 : 23:48:19

quote:
Originally posted by prosit

Hi,

Well i am unable to create a new table, but a temp table did the trick, thanks a bunch

~j

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources