CODEWITHSUNDEEP
sqlsql-serverjoin
Ushah
Ushah

Reputation: 41

Do I use inner join or left join?

I have two separate tables I'm pulling data from with the associate_id being the primary key. I'm trying to find all sales(sales_charge found in sales.dim) made by Associate_ID over several transactions within the last 4 months and the last year. I'm having a hard time with the time stamp and the joins.

Here's what I have so far:

SELECT associate_id
, sales.dim.sales_charge
FROM dbo.associate
LEFT JOIN dbo.sales ahd
ON associate_id = ahd.associate_id
AND ahd.end_dt > GETDATE()

I'm new to SQL and coding in general, please let me know what I'm missing.

Thanks

Upvotes: 2

Views: 69

Answers (2)

Gautam
Gautam

Reputation: 11

Answer to the question "Do I use inner join or left join?" is that you use inner join when you want to include only matching records from both tables, whereas left (outer) join will include all the records from left side table.

In the query you are attempting if you want to have all associates included in the result set even if they do not have any sales in last 4 months, use LEFT JOIN. If you want to have only those associates who have one or more sales then use INNER JOIN.

Another problem is with the condition "ahd.end_dt > GETDATE()". This means all end date after current time. Change it to "ahd.end_dt > DATEADD(month, -4, GETDATE())"

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you want to include all associates, even those with no sales, then use left join:

SELECT a.associate_id, ahd.dim.sales_charge
FROM dbo.associate a LEFT JOIN
     dbo.sales ahd
    ON a.associate_id = ahd.associate_id AND
       ahd.end_dt > DATEADD(month, -4, GETDATE());

Upvotes: 1

Related Questions