Need SQL script for 3 table join

Hey all

I need a MSSQL script that will return the following

PostCode                 SoldTickets
xxxx xxx                           2132

From the following three tables

Events, Customers, Bookings

I need total number of tickets grouped with Post code (whihc is in customer table), tickets are held in bookings under the columns:

Full Tickets
ConcTickets
MembersTickets
CompTickets

So these will need to be added together

Lastly a condition is that the EventID is between 2953 - 2993

Bookings table has a foriegn key for CustID and EventID

Hope this makes sense

Thanks
LVL 7
garethh86Asked:
Who is Participating?
 
brad2575Connect With a Mentor Commented:
This should do it but you may need to put the table name in front of the select values if the same fields are in multiple tables (like I did for PostCode)

select CUSTOMER.PostCode, sum(FullTickets) + sum(concTickets) + sum(MembersITckets) + sum(CompTickets) as TotalTickets
FROM BOOKINGS
INNER JOIN Customer ON BOOKINGS.CUSTID = CUSTOMER.CUSTID
INNER JOIN EVENTS ON EVENTS.EVENTID = BOOKINGS.EVENTID
WHERE EVENTID between 2953 AND 2993
Group By PostCode
0
 
Shannon_LowderCommented:
I believe the following satisfies your request.

---
Shannon Lowder
Database Engineer
SELECT
   postcode, sum(Full Tickets + ConcTickets + MembersTickets + CompTickets) as soldTickets
FROM customers
INNER JOIN bookings
  ON customers.custid = bookings.custid
INNER JOIN events
  ON bookings.eventID = events.eventID
WHERE
   EventID BETWEEN 2953 AND 2993
GROUP BY
   postcode

Open in new window

0
 
Shannon_LowderCommented:
I agreen with brad2575, you may need to put the appropriate table name in front of the columnnames, in addition to wrapping the column names in [].

For example customers.[postcode] and bookings.[Full Tickets]

Since I just noticed the space in the full tickets column name.
---
Shannon Lowder
Database Engineer
0
 
garethh86Author Commented:
Spot on, thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.