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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brad2575Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.