Self-join using multiple conditions-CodePudding

Identify the purchase details of books, where the book is purchased on the same date, exactly on the date Anne has purchased the book. Write a SQL query to display customer’s name and title of the book for the identified purchase details. Do NOT display details of Anne in the query result.

There are these 3 tables for these requirements:

Book:

('B101', 'Science Revolution', 'Journal')
('B102', 'Brain Teasers', 'Aptitude')
('B103', 'India Today', 'Magazine')
('B104', 'Tech World', 'Journal')
('B105', 'Bizz world', 'Magazine')

Customer

('C101', 'Jack')
('C102', 'Anne')
('C103', 'Jane')
('C104', 'Maria')

Purchase:

('P201', 'C101', 'B102', '12-Dec-19')
('P202', 'C102', 'B103', '25-Nov-19')
('P203', 'C103', 'B104', '12-Dec-19')
('P204', 'C104', 'B105', '25-Nov-19') 
('P205', 'C101', 'B101', '11-Dec-19')
('P206', 'C101', 'B106', '12-Dec-19')

I used the following logic for the above requirement

select 
    b.bookname, c.custname 
from 
    customer c 
join 
    purchase p on c.custid > p.custid 
join 
    purchase p1 on p.custid > p1.custid 
                and p.purchasedate = p1.purchasedate 
join 
    book b on b.bookid = p1.bookid 
group by 
    b.bookname, c.custname;

Can anyone provide correct solution? Only 'Maria' 'Bizz world' has to appear in the result.

CodePudding user response：

Step 1

--
-- what did Anne purchase? 
--
select c.custname, p.*
from purchase as p
JOIN customer AS c
  ON c.custid = p.custid
WHERE c.custname = 'Anne';

custname | purchaseid | custid | bookid | purchasedate
:------- | :--------- | :----- | :----- | :-----------
Anne     | P202       | C102   | B103   | 25-Nov-19

Step 2

--
-- Any purchases on the same dates as Anna by other people?
--
select p2.*
from purchase as p1
join customer as c1 
  on c1.custid = p1.custid
JOIN purchase AS p2
  ON p2.purchasedate = p1.purchasedate  -- same date
 AND p2.custid != p1.custid -- different customer
where c1.custname = 'Anne';

purchaseid | custid | bookid | purchasedate
:--------- | :----- | :----- | :-----------
P204       | C104   | B105   | 25-Nov-19

Step 3

--
-- Now get the names from those id's
--
select c2.custname, b2.bookname
from purchase as p1
join customer as c1 
  on c1.custid = p1.custid
join purchase as p2
  on p2.purchasedate = p1.purchasedate
 and p2.custid != p1.custid
JOIN customer AS c2
  ON c2.custid = p2.custid
JOIN book AS b2
  ON b2.bookid = p2.bookid
where c1.custname = 'Anne'

custname | bookname  
:------- | :---------
Maria    | Bizz world

Demo on db<>fiddle here

CodePudding user response：

In similar construct as LukStorms provided, first, lets start with how to get the date that Anne purchased the book. That is the first and foremost requirement for your query. I like to show indentation of the queries how table A gets to B gets to C, etc.

You already had many joins, I am just showing the first part. Get the who, and the date of their purchase. In this case, I am also getting the customer's ID so I know which ID to EXCLUDE from the final answer. Dont rely on knowing their customer ID and knowing you want purchases with the ID greater than the person. What if you have 5000 people, and Anne happened to be person 2789. Doing greater than would fail on any that also purchsed same date but fail on customer ID.

select
        p.custid,
        p.purchaseDate
    from
        customer c
            join purchase p
                on c.custid = p.custid
    where
        c.custname = 'Anne'

So from here, this can be the FIRST alias of the final select because the basis was to have the DATE of the purchase, but also now we KNOW the WHO we DONT want in the result.

select
        AnnePurchase.purchaseDate,
        c2.custname,
        b.bookName
    from
        (select
                p.custid,
                p.purchaseDate
            from
                customer c
                    join purchase p
                        on c.custid = p.custid
            where
                c.custname = 'Anne' ) AnnePurchase
            JOIN purchase p2
                -- first part, join to purchase on same date as Ann purchased her book
                on AnnePurchase.purchaseDate = p2.purchaseDate
                -- but now, get anyone who's customer ID is NOT the same as Anne.
                AND NOT AnnePurchase.custID = p2.custID
                -- now from the 2nd purchase table we can join to the customer and book 
                -- table to get whatever other parts you want
                JOIN customer c2
                    on p2.custid = c2.custid
                JOIN book b
                    on p2.bookid = b.bookid

Notice the in-line comments via -- that show WHY I am doing the join. The AnnePurchase is the primary basis. The join to Purchase (alias p2) is to get based on the same date and NOT the same customer ID as Anne.

From that, its a simple join from the P2 alias back to the customer and book tables. I personally have the indentation of the joins for customer c2 and book b indented under the purchase p2 because THAT is the basis of their relationship.