Using condition in the select clause based on a sub query

Question

Post reply

Using condition in the select clause based on a sub query

Lior.F

SSC Journeyman

Points: 90
More actions
August 26, 2010 at 12:52 am

#237236

I'm trying to achieve the following. Table A holds fake names and i want to replace them with the real ones from table B. But if the actual name doesn't exists i would like to present the fake name.
Table A
ID
FakeName1
FakeName2
Amount
Table B
ID
FakeName
ActualName
Select A.ID,
(SELECT case when Name is NULL then A.FakeName1 else Name end from (Select TOP(1) ActualName from B where B.FakeName = FakeName1) NameTableAlias) ProductName,
(SELECT case when Name is NULL then A.FakeName2 else Name end from (Select TOP(1) ActualName from B where B.FakeName = FakeName2) NameTableAlias) ProductName,
A.Amount
FROM A
When the values are constants it works but when i try show FakeName1 (when the value does not exists in Table B) i get only NULLs.
Thanks
Lior

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Lior

Something like this? (Not tested because you haven't supplied any DDL or sample data.)

SELECT

A.ID

, COALESCE(b.ActualName,A.FakeName1) AS Name1

, COALESCE(b.ActualName,A.FakeName2) AS Name2

, A.Amount

FROM

A

LEFT JOIN

B ON A.ID = B.ID

John

Lior.F SSC Journeyman Points: 90 More actions · Answer 2

Hi John,

Thanks for the reply.

Yes this is the idea but i can't join the tables as the IDs are not correlated. That's why the inner select i tried to do.

Sorry i didn't mention it.

Lior

ralm Hall of Fame Points: 3156 More actions · Answer 3

Try This..

Create Table TableA (ID int identity(1,1),FakeName1 Varchar(20),FakeName2 varchar(20),Amount int)

Create Table TableB (ID int identity(1,1),Actualname Varchar(20),FakeName varchar(20))

Insert into TableA Values('Mike','Johnny',10000),('Ruke','Dirk',10000),('Andy','Micheal',10000)

Insert into Tableb Values('Peter','Johnny'),('Andrews','Andy'),('Stephen','Dirk'),('Murray','Micheal')

Select * from TableA

Select * from TableB

;with P1CTE AS(

Select A.ID,ISNULL(B.ActualName,A.FakeName1) ProductName,amount from TableA A Left Join TableB B

on A.FakeName1 = B.FakeName

),

P2CTE AS(

Select A.ID,ISNULL(B.ActualName,A.FakeName2) ProductName from TableA A Left Join TableB B

on A.FakeName2 = B.FakeName

)

Select A.ID,A.Productname,B.Productname,A.Amount

FROM P1CTE A Inner Join P2CTE B On A.ID = B.ID

Drop Table TableA

Drop Table TableB

^{[font="Verdana"]Regards,
Rals[/font].}

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

Lior

Try this, then. Looks like it might be a good idea to reconsider your database design, as well.

SELECT

A.ID

, COALESCE(B1.ActualName,A.FakeName1) AS Name1

, COALESCE(B2.ActualName,A.FakeName2) AS Name2

, A.Amount

FROM

A

LEFT JOIN

B B1 ON A.FakeName1 = B1.FakeName

LEFT JOIN

B B2 ON A.FakeName1 = B2.FakeName

John

ralm Hall of Fame Points: 3156 More actions · Answer 5

ralm

Hall of Fame

Points: 3156

August 26, 2010 at 1:49 am

#1212115

..

^{[font="Verdana"]Regards,
Rals[/font].}

Lior.F SSC Journeyman Points: 90 More actions · Answer 6

Thanks John

Your last post did the trick.

Tested in all directions.

Lior