CODEWITHSUNDEEP
sqlsql-servert-sql
Saman Gholami
Saman Gholami

Reputation: 3512

Select from multiple tables with group by clause

I have these table in my database : 

Ticket 
 -------------------------------
|ID        int              PK  |
|Paid      varchar(50)          |
 -------------------------------

TicketRow
 ----------------------------------
|ID            int              PK |
|TicketID_FK   int                 |
|SHtimeID_FK   int                 |
 ----------------------------------

I want to fetch the duplicated rows, that have same SHTiemID_FK and have Paid='ok' state in Ticket table, from TicketRow table. I try this :

select SHtimeID_FK,count(*) as cnt from dbo.TicketRow
group by SHtimeID_FK
having count(*)>1

But i don't know how should i add Ticket table in my result set.

UPDATE : I also need Ticket.ID in my resultset

Upvotes: 0

Views: 972

Answers (1)

danny
danny

Reputation: 3266

If I understand your scenario correctly you can simply join these two tables by a inner join as I suppose TicketRow.TicketID_FK is a foreign key to Ticket table.

select SHtimeID_FK,count(*) as cnt 
from dbo.TicketRow as tr inner join dbo.Ticket as t on tr.TicketID_FK=t.ID
where t.Paid='ok'
group by SHtimeID_FK
having count(*)>1

Upvotes: 1

Related Questions