31 Years of Python | 48 Hour Sale Extension!!!
days
hours
minutes
seconds

Less Common Joins non-equi joins

Screen Link:

My Code:

SELECT T.track_id, T.name, COUNT(*) AS no_of_purchases
FROM invoice_line AS il   JOIN track AS t
ON t.track_id=il.track_id
JOIN invoice AS i
ON il.invoice_id=i.invoice_id
WHERE i.invoice_date like '%2020%'

What I expected to happen:
Provide a list of all songs in track and how many times they were purchased in 2020

What actually happened:

[1 rows x 3 columns]
track_idnameno_of_purchases149Black Sabbath1150

The solution is below:
SELECT t.track_id, t.name,
COUNT(i.invoice_id) AS no_of_purchases
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
LEFT JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE ‘2020%’
GROUP BY t.track_id, t.name;
I didn’t get why count(*) would not be used in this situation, why left join was used. I somewhat get why group by was used but I thinking it would be Group by invoice_id

Here’s a version of your query focused on just one track:

SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
  FROM track AS t
  LEFT JOIN invoice_line AS il
    ON t.track_id = il.track_id
  LEFT JOIN invoice AS i
    ON il.invoice_id = i.invoice_id AND invoice_date LIKE '2020%'
WHERE t.track_id = 1;
track_id name invoice_id invoice_date
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You)
1 For Those About To Rock (We Salute You) 496 2020-04-01 00:00:00

The empty cells tell you that the invoices to which that track belongs are not from 2020. When you do COUNT(*), you’re counting all the rows when you just wanted to count the one on the bottom because it’s the only one that matches the criteria.

Another issue with your query is the lack of GROUP BY. Please review Aggregate Queries with Non-Aggregate Result Columns.

hi Bruno thank you so much for the explanation. I had one question though what if instead of Left Join I did Inner join , in that scenario would count(*) work as expected?

Can you please provide the query you have in mind? I can’t tell with just that sentence.

You can also just try it and see if it passes the screen.

hi Bruno,

I was wondering if in this query
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
LEFT JOIN invoice_line AS il
ON t.track_id = il.track_id
LEFT JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE ‘2020%’
WHERE t.track_id = 1;

instead of left join you used Inner join
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
JOIN invoice_line AS il
ON t.track_id = il.track_id
JOIN invoice AS i
ON il.invoice_id = i.invoice_id AND invoice_date LIKE ‘2020%’
WHERE t.track_id = 1;

Would count(*) have worked and counted only those rows where invoice_date is 2020 ?

No, if you do inner joins, then you lose the ability to count the missing tracks, which should be present with the value of 0.

hi ,
would it be possible to share a resource for working of join on more than 2 tables because I am struggling with visualizing it. According to me the first time inner join will only show records which are there in track and invoice-line
SELECT t.track_id, t.name, i.invoice_id, i.invoice_date
FROM track AS t
JOIN invoice_line AS il
ON t.track_id = il.track_id’
Result:
Track-id Track name invoice_id invoice_date
1 rock 1 2021
2 punk 2 2020
3 electric 3 2020
4 pop 4 2022

then when we join invoice_line with invoice table we are only going to show records which are having invoice_id same as in invoice_line and invoice date like 2020

it should show only 2 records from the above result

Sorry, your query doesn’t run and I don’t get what you mean.

Hello ,

I have mostly understood the concept now I just want to understand
SELECT t.track_id,t.name, count(i.invoice_id) AS no_of_purchases
FROM track AS t LEFT JOIN invoice_line AS il
ON t.track_id=il.track_id
LEFT JOIN invoice AS i
ON i.invoice_id=il.invoice_id
AND i.invoice_date like ‘2020
GROUP BY t.track_id,t.name
why did we use invoice_id from invoice table and not from invoice_line

I don’t know what you mean. Both were used.

It’s already really hard to understand your questions because the code isn’t properly formatted. Please format it properly in the future.