asked on

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

ASKER CERTIFIED SOLUTION

brad2575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shannon_Lowder

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

Shannon_Lowder

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

garethh86

ASKER

Spot on, thanks!