CODEWITHSUNDEEP
sqlsql-server
Messi
Messi

Reputation: 51

Retrieve data from two tables with missing links

I have a situation where to retrieve data with specific criteria and the only relationship between two tables is ID. See the tables below. The target join for this case should be co_id, however the table2 doesn't have this column. I need to retrieve anyone in co_ID with LDR=1 and email='' and Desig ='0'. in the exam below the result should be '0000383' in DD 0000 no one have desg 1 and LDR is one but email is null.

Simple syntax missing the join part. 

select Desig, LDR,Table2.*  
from  Table1, Table2 
where (LDR =1 and email ='') and (Desig = 0)

Table1 
id      Desig LDR    
0000121  0     0        
0000383  0     1                
0000509  0     1       
0000593  1     0      
0000626  1     0
0000526  0     1
0000523  0     0

------------------------------
Table2
id     | co_id   |  FULL_NAME    |     EMAIL
0000121  DD 0000    Mary Jen    
0000383  DD 0000    Mat Howard       
0000509  YY 0000    Dorothy Bolan    [email protected]
0000593  YY 0000    Pat Schu    
0000626  XX 0000    Lisa m    
0000526  XX 0000    Lori de
0000523  XX 0000    Donna tr

To sum it what i need is to retrieve id with ldr = 1 and email = ' ' if desig = 0 for each co_id. hope this make more sense.

Upvotes: 0

Views: 82

Answers (2)

Pugal
Pugal

Reputation: 549

It gives me correct result for your data.

create table #table(id varchar(10),Design int,LDR int)

create table #table1(id varchar(10),co_id varchar(10),fname varchar(10),email varchar(20))

insert into #table values('0000121',0,0),('0000383',0,1),('0000509',0,1),('0000593',1,0)

insert into #table1 values('0000121','DD 0000','Mary Jen' ,''),('0000383','DD 0000','Mat Howard' ,''),('0000509','YY 0000','Dorlan' ,'[email protected]'),('0000593','YY 0000','Pat Schu' ,'')

select  t1.Design,t1.LDR,t2.* from #table t1 join #table1 t2 on t1.id = t2.id and t1.LDR = 1 and t2.email ='' and t1.Design = 0

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

You can join from one column of one table to multiple columns in a second table like this:

select * from table1 a join table2 b on a.id=b.id or a.id=b.co_id  
where  LDR =1 and email =''  and  Desig = 0

Upvotes: 1

Related Questions