SQL Joins , exclude unwanted records without using WHERE 4

[1DMF]

Hi,

Ok , possibly a stupid question coming up and feel free to tell me if it is ;-)

I have a query...

Set rs = CurrentDb.OpenRecordset("SELECT dbo_Business_Register.[Prod_Type],dbo_Business_Register.[Rec_Type],dbo_Business_Register.[Commission],Contacts.[CompanyName] FROM [dbo_Business_Register] LEFT JOIN [Contacts] ON Contacts.[Membership Number] = dbo_Business_Register.[Adv_MemNo] WHERE ([Ins_Lnk] is null) AND (Sub_Date BETWEEN #" & SDate & "# AND #" & EDate & "#) AND ([Status] = 'SUB') AND [Rec_Type] <> 'LOAN'", dbOpenSnapshot, dbSeeChanges)

The senario is that in the [dbo_Business_Register] table there are some dummy records for Adv_MemNo = 'Test Account'.

However, in the actual members database [Contacts] there is no record for [Membership Number] = 'Test Account'

I'm assuming because of LEFT JOIN on [Membership Level] = [Adv_MemNo] , that as there will be no match for the Test Account, those records will not be selected , is this correct?

How does LEFT vs RIGHT work? and does it make no difference?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

I'm assuming because of LEFT JOIN on [Membership Level] = [Adv_MemNo] , that as there will be no match for the Test Account, those records will not be selected , is this correct?

No. This is not correct. If you use Inner Join, then your statement would be correct.

Inner Join will ONLY return rows that match the ON clause.

Left Join will return all rows from the left table and matching rows from the right table.

Right Join will return all rows from the right table and matching rows from the left table.

[tt][blue]TableOnTheLeft [Inner/Left/Right] Join TableOnTheRight[/blue][/tt]

By 'Left table', I mean... the table that appears to the left of the keyword JOIN. The right table is the table that appears to the right of the keyword JOIN.

Left join and right join are very similar.

TableA Left Join TableB

is the same as....

TableB Right Join TableA

Because left and right are so similar, most programmers use Left Join. Not because it's better, but because it's simpler to think about when you follow a consistent approach to creating queries.

That being said.... you need to be a little careful when you WANT left join functionality. I mean... it's easy to use left join instead of inner join, but there is a hidden gotcha that you need to think about.

When you left join tables, if there is no match from the right table, you will still get rows, but all the columns from the right table will contain NULL. Now, if you have a where clause with a filter condition on the right table, you will not match the NULL's and effective turn your left join query in to a right join (because all non-matching rows will have NULL and you would effectively remove them with the where clause).

In your query, you have 4 where clause conditions, Ins_Lnk, Sub_Date, Status, and Rec_Type. If any of these 4 columns exist in the Contacts table, you have effectively created an inner join instead of left join.

Not to worry though, because it's easy enough to fix. To handle this situation, just put the where clause criteria in to the ON clause. Like this...

SELECT dbo_Business_Register.[Prod_Type],
       dbo_Business_Register.[Rec_Type],
       dbo_Business_Register.[Commission],
       Contacts.[CompanyName] 
FROM   [dbo_Business_Register] 
       LEFT JOIN [Contacts] 
          ON  Contacts.[Membership Number] = dbo_Business_Register.[Adv_MemNo] 
          AND ([Ins_Lnk] is null) 
          AND (Sub_Date BETWEEN #" & SDate & "# AND #" & EDate & "#) 
          AND ([Status] = 'SUB') 
          AND [Rec_Type] <> 'LOAN'

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Yes thanks George.

though I notice you haven't used a WHERE clause, does the ON operator act as the WHERE so you can just continue with the AND

By the way there aren't fileds in either table named the same, so it shouldn't be a problem.

I'll run some tests and get back to you

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

does the ON operator act as the WHERE

They are similar, but not the same. In the where clause, you will only get rows that match. In the ON clause, you can still get your NULL from the left join.

By the way there aren't fileds in either table named the same, so it shouldn't be a problem.

That doesn't matter for the purposes of this discussion.

Please read:
thread183-1504081

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

Yup spot on, INNER JOIN was what I needed, much appreciated.

curiosity what's OUTER JOIN

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

Outer is an optional keyword.

The types of joins are:

Inner, Left, Right, Full, and Cross.

You seem to understand Inner, Left, and Right.

A full join is a combination of left and right. You get all rows from both tables and matching rows from either one.

A cross join has no ON clause and returns every combination of rows from both tables.

Ex:

-- Setup some sample data
Declare @Table1 Table(Id Int, Fruit VarChar(20))

Insert into @Table1 Values(1, 'Apple')
Insert into @Table1 Values(2, 'Banana')
Insert into @Table1 Values(3, 'Grape')

Declare @Table2 Table(Id Int, Color VarChar(20))

Insert Into @Table2 Values(1, 'Red')
Insert Into @Table2 Values(2, 'Yellow')
Insert Into @Table2 Values(4, 'Purple')

Select *
From   @Table1 As Table1
       Full Join @Table2 As Table2
         On Table1.Id = Table2.Id

[green]/* Results
Id          Fruit                Id          Color
----------- -------------------- ----------- --------------------
1           Apple                1           Red
2           Banana               2           Yellow
NULL        NULL                 4           Purple
3           Grape                NULL        NULL
*/[/green]

Select *
From   @Table1 As Table1
       Cross Join @Table2 As Table2

[green]/* Results
Id          Fruit                Id          Color
----------- -------------------- ----------- --------------------
1           Apple                1           Red
2           Banana               1           Red
3           Grape                1           Red
1           Apple                2           Yellow
2           Banana               2           Yellow
3           Grape                2           Yellow
1           Apple                4           Purple
2           Banana               4           Purple
3           Grape                4           Purple
*/[/green]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

FULL & CROSS look a bit bizzarre in terms of results, I cannot think in my DB structure why I would use that type of query.

The reason I ask about OUTER is I have used it to acheive my goal , but am trying to understand why I need the option OUTER for the query to work...

 my @adv = &getSQL("Business_Register LEFT OUTER JOIN Members ON Business_Register.Adv_MemNo = Members.ID","DISTINCT Adv_MemNo,FirstName,LastName","1=1","LastName ASC");

This translates to SQL as...

SELECT DISTINCT Adv_MemNo,FirstName,LastName 
FROM Business_Register LEFT OUTER JOIN Members
WHERE 1=1 
ORDER BY LastName DESC

why do I need outer to make it work? based on the LEFT it should get all records from business_register and only those from Members that match leaving NULL in those that don't

But it only works if I include the OUTER operator






"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

oh for an edit on this forum.....

SELECT DISTINCT Adv_MemNo,FirstName,LastName 
FROM Business_Register LEFT OUTER JOIN Members
ON Business_Register.Adv_MemNo = Members.ID
WHERE 1=1 
ORDER BY LastName ASC

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[1DMF]

oh for an edit on this forum.....

SELECT DISTINCT Adv_MemNo,FirstName,LastName 
FROM Business_Register LEFT OUTER JOIN Members
ON Business_Register.Adv_MemNo = Members.ID
WHERE 1=1 
ORDER BY LastName ASC

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

No.

[tt][blue]SELECT DISTINCT Adv_MemNo,FirstName,LastName
FROM Business_Register LEFT OUTER JOIN Members
WHERE 1=1
ORDER BY LastName DESC[/blue][/tt]

That does not run because left join requires an ON clause, and you don't have one.




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

ok i found this info but i'm still struggling...

The difference is subtle, but it is a big difference. The ON condition stipulates which rows will be returned in the join, while the WHERE condition acts as a filter on the rows that actually were returned.

Simple example: Consider a student table, consisting of one row per student, with student id and student name. In a second table, a list of grades that students have received, with student_id, subject, and grade. Give me a list of all students, and show their grade in Math. This requires a LEFT OUTER JOIN, because you want all students, and you know that some of them didn't take Math. Here are two queries:

select name
, grades.grade as math_grade
from students
left outer
join grades
on students.id
= grades.student_id
where grades.subject = 'Math'
select name
, grades.grade as math_grade
from students
left outer
join grades
on students.id
= grades.student_id
and grades.subject = 'Math'
Now for the crucial difference: the first query returns only those students who took Math, and those who didn't are not included. In the second query, all students are included, and those who took Math have their grade shown.

Why the difference? In the first query, the LEFT OUTER JOIN returns all students, even if they didn't take Math. If they didn't take Math, then the joined row that is returned by the LEFT OUTER JOIN will have NULLs in all the columns from the grades table. But then for each such joined row returned, the WHERE clause comes along and picks only those rows which are Math. And since NULL isn't equal to anything, students who didn't take Math disappear from the results.

In the second query, the join condition means that students who took Math are returned, or else NULL because it's a LEFT OUTER JOIN. So all students are included in the results, because there's no WHERE clause to filter them out. Their Math grade will be their Math grade or else NULL.

In effect, the first query behaves the same as an inner join. Only the matched rows are retained after the WHERE clause has done its job. Why bother returning rows to the WHERE clause that you want filtered out? Make it an INNER JOIN and save some needless processing. Of course, if you do want a LEFT OUTER JOIN, make sure that any filter conditions on the right table are in the ON clause, not the WHERE clause.

I was about to go on anout how that makes no sense , then I think I just had a eureka moment.

Where filters the results after all records are returned, which will only contain joined records euqlling 'Math'

However, the ON using AND , filters the second (right) table to only return those equalling 'Math', but returns all records from LEFT table.

I still can't get what OUTER means, different than just using LEFT JOIN

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

I still can't get what OUTER means, different than just using LEFT JOIN

OUTER makes no difference. It's an optional keyword.

Left Outer Join is exactly the same as Left Join




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

aha, no wonder I was scrathing my head!

Two stars for you * *

I was thinking there was 3 types...

Left Join
Left Inner Join
Left Outer Join

lol - though I just read that in MS Access the default is INNER not OUTER, which you seem to indicate OUTER is the default.

So is it system specific as to whether INNER or OUTER are default?

I guess it's just easier to be sure and user either INNER or OUTER as required, can't make a mistake then!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[gmmastros]

No.

There is no such thing as a Left Inner Join

There is only Left Join and Left Outer Join, both of which are the same thing.

The only type of join are:

Inner Join

Left Join
Left Outer Join

Right Join
Right Outer Join

Full Join
Full Outer Join

Cross Join

In all cases, outer is optional. Technically, Inner is optional too.

If you do not specify the type of join, it defaults to inner.

Ex:

-- Setup some sample data
Declare @Table1 Table(Id Int, Fruit VarChar(20))

Insert into @Table1 Values(1, 'Apple')
Insert into @Table1 Values(2, 'Banana')
Insert into @Table1 Values(3, 'Grape')

Declare @Table2 Table(Id Int, Color VarChar(20))

Insert Into @Table2 Values(1, 'Red')
Insert Into @Table2 Values(2, 'Yellow')
Insert Into @Table2 Values(4, 'Purple')

Select *
From   @Table1 As Table1
       Join @Table2 As Table2
         On Table1.Id = Table2.Id

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[1DMF]

right , got it!

LEFT & RIGHT irrelivant for INNER as it selects only matching records from both.

and specifying L or R auto makes it an OUTER

Finally the penny drops!

Many thanks.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[ESquared]

Since you're using pound signs around your dates I presume you're actually querying an Access database, not SQL Server?

In either case you should be escaping your date strings if they come from user input...

 

[1DMF]

# are 'hashes', pound signs are £ ;-)

The answer is yes & no.

That specific post using the date range was in MS Access but the queries are also performed in PERL.

my @adv = &getSQL("Business_Register LEFT OUTER JOIN Members ON Business_Register.Adv_MemNo = Members.ID","DISTINCT Adv_MemNo,FirstName,LastName","1=1","LastName ASC");

That's PERL code.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[ESquared]

# are 'hashes', pound signs are £ winky smile

Fair enough, though wikipedia says

The "#" sign is sometimes called "pound sign" in non-sterling countries.

It is common, when calling banks or other companies and getting their automated phone system, to hear "press pound" and "press star.

 

[1DMF]

though wikipedia says

oh if wiki say's it it must be true lol ;-)

In the UK it's "Press Hash", it's like if we started calling the '&' a dollar sign!

£ = Pound
# = Hash
$ = Dollar
€ = Euro

Would be alot easier if we all called em the same thing wouldn't you say!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!

 

[r937]

When you left join tables, if there is no match from the right table, you will still get rows, but all the columns from the right table will contain NULL. Now, if you have a where clause with a filter condition on the right table, you will not match the NULL's and effective turn your left join query in to a right join (because all non-matching rows will have NULL and you would effectively remove them with the where clause).

this is a very clear description, except i think you accidentally said "turn your left join query in to a right join" when you meant to say "turn your left join query into an inner join"

ok i found this info ...

thanks, i wrote that


by the way, # is called an octothorpe



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon