CODEWITHSUNDEEP
sqlconditional-statements
darma
darma

Reputation: 25

Get same column twice with different conditions in SQL

I would like to retrive same columns twice but with different conditions.

my Query is like this but the following Query retrives two different columns. But I want to get the same column twice, How can I achieve this?

Select name from tbCustomer where ID in (select ID from tbOldCustomer where oldID= 1)
Select name from tbCustomer where ID in (select ID from tbOldCustomer where oldID= 2)

tbCustomer has two columns:
ID name
1   aaa
2   bbb
3   ccc
4   ddd

tbOldCustomer has two columns:

ID oldID
1   2
2   1
3   1
4   2
4   1

would like to get name where oldID in (1, 2) and the output should be the follwing:

name name1
bbb   aaa
ccc   ddd
ddd
 

Upvotes: 0

Views: 865

Answers (6)

JB's
JB's

Reputation: 626

Try this

SELECT  name 
FROM tbCustomer tb1 
JOIN (SELECT ID FROM tbOldCustomer WHERE oldID = 1 OR oldID= 2) tb2
     ON tb1.ID = tb2.ID
WHERE tb1.ID IN (SELCT ID from tbOldCustomer where oldID in (1, 2))

Upvotes: 0

Hemang A
Hemang A

Reputation: 1012

Please try this.

Select name from tbCustomer where ID in (select ID from tbOldCustomer where oldID IN(1,2))

OR

Select A.name from tbCustomer A
INNER JOIN tbOldCustomer  B
ON A.id = B.Id
AND B.OldId In (1,2)

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use exists

select t1.name 
from tbCustomer t1 
exists( select 1 from  tbOldCustomer t2  where  t1.id = t2.id
                                       and t2.oldid in (1,2)
        )

Upvotes: 1

Srusti Thakkar
Srusti Thakkar

Reputation: 1901

You have to try 

Select tc.name, toc.name from tbCustomer tc inner join 
tbOldCustomer toc On toc.id = c.id where toc.oldID IN (1,2)

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

you can try using conditional aggregation with case when expression

select max(case when oldID= 1 then name end) as name1,
       max(case when oldID= 2 then name end) as name2
from tbCustomer join tbOldCustomer on ID=oldID where oldid in (1,2)
group by oldid

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

I think you can achieve that by using simply JOIN

Select name 
from tbCustomer tc 
inner join tbOldCustomer toc On toc.id = tc.id
where toc.oldID IN (1,2)

Upvotes: 1

Related Questions