February 27, 2015 at 3:18 pm
Hi,
Below is the sample data with my trydeclare @idcomp int = 300
declare @comp table(idcomp int , name varchar(100));
declare @clientMessage table(IdClient int, idcomp int,message varchar(100));
declare @compMessage table(Id int, idcomp int,message varchar(100));
insert into @comp
select 100,'IBM' union all
select 200 ,'oracle' union all
select 300, 'Microsoft';
insert into @compMessage
select 1,100, 'Message10' union all
select 2 ,200, 'Messge2' union all
select 3 ,300, 'Messge3';
insert into @clientMessage
select 1,100, 'Message1' union all
select 2 ,200, 'Messge2'
SELECT coalesce(CM.message,CMT.message)
FROM @compMessage CM
JOIN @comp C ON (C.idcomp = CM.idcomp)
JOIN @clientMessage CMT ON (CMT.idcomp = C.idcomp)
where c.idcomp = @idcomp
I need to check the @compMessage table and if message exits then select message .if not exists then select message from @clientMessage.
Am i doing anything wrong here. I am supposed to get "'Messge3'" as result. but am getting empty row. because i don't have 300 as idcomp in @compmessage table.
any suggestion how to tweak this
February 27, 2015 at 9:38 pm
This is my try and working fine for meSELECT coalesce(CM.message,CMT.message)
FROM @comp C
LEFT JOIN @compMessage CM ON CM.idcomp = C.idcomp
LEFT JOIN @clientMessage CMT ON CMT.idcomp = C.idcomp
where c.idcomp = @idcomp.
Any suggestion/alternative please
February 28, 2015 at 1:32 am
Alternative solution which is more efficient but behaves slightly differently as it will bring back messages from both tables if they exist.
😎
SELECT
CM.message
FROM @compMessage CM
WHERE CM.idcomp = @idcomp
UNION ALL
SELECT
CL.message
FROM @clientMessage CL
WHERE CL.idcomp = @idcomp
;
Another alternative if columns from the @comp table are needed in the output, the difference here is that there is only one join operator, should perform better on larger sets.
SELECT
CP.name
,X.message
FROM @comp CP
CROSS APPLY (
SELECT
CM.message
FROM @compMessage CM
WHERE CM.idcomp = CP.idcomp
UNION ALL
SELECT CL.message
FROM @clientMessage CL
WHERE CL.idcomp = CP.idcomp
) AS X
WHERE CP.idcomp = @idcomp;
February 28, 2015 at 6:47 am
Thank you Eirik.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply