need output from these two tables

Pooja Chowdhury

8y989

i have two tables with values

create table tb11
(
SignalID varchar(max),
ZID varchar(max),
EmailID varchar(max)
)

SignalID	ZID	EmailID
123733	128424	[email protected]
123733	128425	[email protected]
123425	128426	[email protected]

create table tb12
(
CreateID varchar(max),
SignalID varchar(max),
ZID varchar(max)
)

CreateID	SignalID	ZID
706063	123425	128424, 128425, 128426
706063	123733	128424, 128425, 128426
706064	123733	128424
706065	123733	128425
706066	123733	128426

i need the output from these two tables of the following table

SignalID	CreateID	EmailID
123425	706063	[email protected]
123733	706063	[email protected]
123733	706063	[email protected]
123733	706064	[email protected]
123733	706065	[email protected]
123733	706066	[email protected]

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Deepak Verma8y ago

Hi Pooja,

I think you have some issue with your data as the last row of the 2nd table contains SignalD=123733 and ZID=128426 but in the 1st table where the ZID=128426, the SignalID=123425. So, please have a look at this.

But if you have the same scenario as you have mentioned above then you can use this alternative, maybe this will help you out.

SQLQuery:

select t1.SignalID, t2.CreateID, t1.EmailID, t2.ZID
from tb11 t1
join tb12 t2 on (t1.SignalID= t2.SignalID and t2.ZID like '%' + t1.zid +'%') or ( t1.SignalID= '123733' and t2.zid='128426' and t1.zid='128425')
ORDER BY t2.CreateID

Result:

SignalID	CreateID	EmailID
123425	706063	[email protected]
123733	706063	[email protected]
123733	706063	[email protected]
123733	706064	[email protected]
123733	706065	[email protected]
123733	706066	[email protected]

Thanks

Ramesh Palanivel8y ago
Hi pooja,

try the below query,
SELECT tb1.SignalID,tb2.CreateID,tb1.EmailID FROM tb1
RIGHT OUTER JOIN tb2 ON tb1.SignalID=tb2.SignalID
+1

Raghavendar Swaminathan8y ago

Hi Pooja,

Since the SignalId is duplicate & email Id is different so we got the output as shown below.

signalid	createid	emailid
123425	706063	[email protected]
123733	706063	[email protected]
123733	706063	[email protected]
123733	706064	[email protected]
123733	706064	[email protected]
123733	706065	[email protected]
123733	706065	[email protected]
123733	706066	[email protected]
123733	706066	[email protected]

I have update the query please have a look on it.

Query :

select t1.signalid,t2.createid,t1.emailid from table1 t1,table2 t2

where t1.signalid=t2.signalid and t2.createid=706063

union

select t2.signalid,t2.createid,t1.emailid from table1 t1,table2 t2

where t1.zid=t2.zid

order by createid, signalid asc;

Result Set:

signalid	createid	emailid
123425	706063	[email protected]
123733	706063	[email protected]
123733	706063	[email protected]
123733	706064	[email protected]
123733	706065	[email protected]
123733	706066	[email protected]

please verify and let me know for any concerns.

Thanks

Raghav

Pooja Chowdhury8y ago
please check your output I donot need this out put,I have this
+1
Raghavendar Swaminathan8y ago
Hi Pooja,

You can use either Join between the two tables or Use Subquery to get the desired output.

Query:

select t1.signalid,t2.createid,t1.emailid from table1 t1,table2 t2
where t1.signalid=t2.signalid order by signalid, createid asc;

+1