SQL Query for joining 5 tables

Question

I have five tables:

Cutomer(CustomerID, name, surname)

PaymentMethod(cardNo,securityCode,expirationDate)

Ticket(ticketID, parkingnumber)

Has(CustomerID, CardNo)

Reserve(TicketID,cardNo)

How can I write SQL query in pgadmin-4 for listing the customers who have reserved exactly two tickets?

Thank you for any help!

Do you have any sample data, with which you can then show what results you'd expect to get from that sample? And what have you tried? If you can show some SQL that you tried, we can explain why it didn't work, what to change, etc. What is generally frowned upon, however, are questions that just ask us to do a homework assignment ***for*** you. — MatBailie, Jul 05 '21 at 13:30

score 0 · Answer 1 · answered Jul 05 '21 at 13:46

This is basic INNER JOINs with a grouping function (GROUP BY) to count tickets per customer.

Then you can use the COUNT function in a HAVING clause to apply a specific condition.

Something like :

SELECT
    c.CustomerID,
    COUNT(t.TicketID)
FROM
    Cutomer c
    INNER JOIN Has h ON h.CustomerID = c.CustomerID
    INNER JOIN Reserve r ON r.cardNo = h.CardNo
    INNER JOIN Ticket t on t.TicketID = r.TicketID
GROUP BY
    c.CustomerID
HAVING
    COUNT(t.TicketID) = 2

score 0 · Answer 2 · answered Jul 05 '21 at 14:51

You can use joins and group by, but you don't need all the tables. If I understand the data model, you only need reserve and has:

select h.customerid, count(*)
from reserve r join
     has h
     on r.cardno = h.cardno
group by h.customerid
having count(*) = 2;

SQL Query for joining 5 tables

2 Answers2