CODEWITHSUNDEEP
sql-server
user10054130
user10054130

Reputation:

SQL COLUMN ERROR for JOIN

I have a Dtltable 

tabid        TickNUM    TickType      Amount

 001-FGF     C2001        Credit      133
 001-FGF     Tk002        Token        23
 001-FGF     Tk003        Token        43

Is there anyway, i can pull all the tabid data using single TickNum, coz the tabid is same for all TickNum.

Select * from Dtltable
where tickNum = 'C2001'

but it displays only particluar TickNum row. I need all rows with similar tabid's as well. Not sure how to write logic.

Upvotes: 0

Views: 56

Answers (3)

Chandan
Chandan

Reputation: 235

Though this is a bit long, you can achieve this by first selecting distinct data, then by using inner join. Below how I did that. 

CReate table #temptab
(
tabid nvarchar(20) null,
ticknum nvarchar(10)null,
ticktype varchar(20)null
)

Create table #temptab1
(
tabid nvarchar(20)null,
ticknum nvarchar(10)null,
ticktype varchar(20)null
)

insert into #temptab1(tabid,ticknum)
select distinct tabid , ticknum  from #temptab where ticknum='C2001' // you can pass @param instead of hardcoded value.

//below line, where you will get data:

select t.tabid,t.ticknum,t.ticktype from #temptab1 t1 inner join #temptab t on t1.tabid= t.tabid

Upvotes: 0

pfx
pfx

Reputation: 23224

Using a JOIN

SELECT d1.* 
FROM Dtltable d1
INNER JOIN Dtltable d2 ON d2.tabid = d1.tabid AND d2.TickNUM = 'C2001'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269603

One method is a subquery:

select d.*
from dtltable d
where d.tabid = (select d2.tabid from dtltable d2 where d2.tickNum = 'C2001');

If the subquery could return more than one row, use in instead of =.

Upvotes: 1

Related Questions