Solved

# Sql Criteria one to many

Posted on 2011-03-11
374 Views
Hello
I know this may be very simple but my brain does not seem to work today.
I have a sql query joining table A to Table B
it is a one to many where Table A as the unique records and table B has the multiple records that tie to each single record in A.
for example Table A  stores Tickets with a Ticket ID in it
Table B has all the detail items on that Ticket.
what i need to do is report on is in a time period
how many tickets there were issued and how many items per ticket.

So if there were 10 tickets last month with 40 items
i need to report the result as 10 tickets and 40 items.
what i have reporting know for a ticket count is 40  because it counts each detail record for each ticket.

How can i do this?
0
Question by:schuitkds
• 2
• 2

LVL 32

Accepted Solution

ewangoya earned 500 total points

select count(ticket), count(Items)
from Tablea
inner join tableb on Table1.ticket = tableb.ticket
group by tablea.ticket
0

LVL 18

Expert Comment

WITH cteTicketList (TicketID)
AS (SELECT TicketID FROM TicketList WHERE TicketDate BETWEEN @StartDate AND @EndDate),
cteTicketDetail (TicketID, Qty)
AS
(
SELECT TicketID, COUNT(*) AS Qty
FROM TicketDetail
WHERE TicketID IN (SELECT TIcketID FROM cteTicketList)
GROUP BY TicketID)
SELECT TicketList.*, cteTicketDetail.Qty
FROM TicketList
INNER JOIN cteTicketList ON TicketList.TicketID = cteTicketList.TicketID
LEFT OUTER JOIN cteTicketDetail ON TicketList.TicketID = cteTicketDetail.TicketID
0

LVL 32

Expert Comment

Or

select count(TableA.ID) as Tickets, (Select Count(ID) Items from tableB
where TableB.ID = TableA.ID
group by TableB.ID) Items
from TableA
group by ID
0

LVL 18

Expert Comment

My solution sales to any size of database.  If you have a million tickets in the table, you don't want to be looking at all of the detail on all of the orders if you are only interested in a small subset.
0

LVL 1

Expert Comment

Use an ordered analytical function, as follows:

COUNT(TicketID) OVER(PARTITION BY TicketID, NEWID()) AS TicketCount,
COUNT(dbo.tbl_Ticket_Detail.TicketDetailID) OVER (PARTITION BY TicketID) AS DetailCount
INNER JOIN dbo.tbl_Ticket_Detail WITH (NOLOCK)
0

## Featured Post

### Suggested Solutions

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.