INNER JOIN and LEFT JOIN question... 1

[davism]

Hi,

This is a little interesting to try and explain...

What I am trying to do is run a query... but what I'm finding is that on under some conditions an INNER join on soem tables are necessary.

However, on other conditions WHERE the USE of the tables are NOT applicable it will not return anything (reason: the INNER JOIN and that makes sense.) But I need the information WHERE the USE of the tables are not applicable.

I thought a LEFT JOIN would be the answer BUT a LEFT JOIN is providing more data. And I only need one row out of that. (That the INNER JOIN provided.)

So, basically I want to do an INNER JOIN on some cases and a LEFT JOIN on others where it is really not applicable. How can I do something like that?

Any info would be greatly appreciated.

Thanks

 

[bborissov]

Sure, just make a SP where you could handle this.
Other way is ALWAYS to use LEFT JOIN, but where you need INNER JOIN you should put something in WHERE clause addressing JOINED table that you must be sure ALWAYS be true
Something like that

1. When you need LEFT JOIN:

SELECT .....
FROM ...
LEFT JOIN SomeTable ON ....
...

2. Where you need INNER JOIN:

SELECT .....
FROM ...
LEFT JOIN SomeTable ON ....
WHERE SomeTable.SomeFied IS NOT NULL --( and that is a field that NOT allow NULLs)
...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[davism]

Ok, what will a LEFT JOIN actually do?

I have a table that have something like:

a NULL
b NULL
c NULL
a 42

My LEFT join is joining on the column that represents the character and I have an AND that specifies the integer: 42.

So, I have like a:

LEFT JOIN TableA on TableB.Column1 = TableA.Column1
AND TableA.Column2 = 42

But what appears to happen is I get:

a NULL
b NULL
c NULL
a 42

IF I do an INNER JOIN I get:

a 42

THEN there I conditions where I would NOT use the LEFT JOIN information at all.

Make sense?

 

[bborissov]

If you put:

SELECT .....
FROM ...
LEFT JOIN JoinedTable
WHERE JoinedTable.Column2 = 42

That LEFT join is become INNER, just because ALL non matching records (including NULL ones) are filtered out.

OK, just give me and example from BOTH tables and desired result (based on that example data) when you need LEFT and INNER joins.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[davism]

You confused me.

A WHERE statement on a LEFT JOIN? Do you mean an ON condition?

Because I tried that with the AND on the left join. It did not work.

 

[bborissov]

NO!!!!
That is the whole point. If you need INNER JOIN you put something in WHERE, If you need LEFT JOIN you put NOTHING in WHERE.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[davism]

Oh gotchya... DUH! Reading too much into that one.

I used an INNER JOIN with the WHERE (I had to because the changing the INNER JOIN to a LEFT JOIN produced more data.) So, I went with an INNER JOIN and a WHERE.

The problem now and mentioned before is with the other situation that DOES NOT USE those is now bound by the WHERE situation.

 

[gmmastros]

Ok, what will a LEFT JOIN actually do?

With a left join, you are guaranteed to get AT LEAST one row in the results for each row in the left table. If there are no matches (based on the join condition) with any row in the right table, you will still get a row from the left table, but the columns from the right table will all contain null. If there is a single match between the 2 tables, you will get one row with the data from each table (similar results to an inner join). If there are multiple matches, you will get multiple rows.

Let me restate part of this... The ONLY way to get all nulls from the right table is if there is no match.

So....
no match - You get 1 row from left table and null's from right table
1 match - you get 1 row with data from both tables.
multiple matches - you get multiple rows.

The only difference between a left join and an inner join occurs when there is no match. An inner join would not return that row and a left join would return it.

Does this help clear up some of the confusion?

Like Boris said earlier, post some sample data from both tables and the expected output so that we can help you better.


-George

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

 

[davism]

Here is a sample.

Table A
column a column b
a NULL
b NULL
c NULL
a 42

Table B
column a column b
List NULL
Sample b
Offer NULL
Service a

Table C
column a column b
10 NULL
20 Sample
30 Offer
40 Service

There are situations where I need to pull like
Service and you see column b has a value. In that situation I do not want to use Table A.

So, what I am doing is something like

SELECT * from TABLE C
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=40
AND TableA.ColumnB IS NOT NULL

This would give me the record with the 42.

However I want to do something like:
SELECT * from TABLE C
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=30
AND TableA.ColumnB IS NOT NULL

This should give me the values and nothing from TableA but it doesn't.

The WHERE from the "AND TableA.ColumnB IS NOT NULL" causes issues in that nothing is reported.

 

[davism]

gmmastros,

Yep, after I sent that I realized I needed to provide that sample.

I tried on that last post.

 

[davism]

Something the effect of in the WHERE an

AND IF-ELSE sort of situation?

 

[davism]

Maybe not. I have no actions on the when the boolean is met or not on an IF-ELSE type situation. :-(

 

[gmmastros]

TableA has 2 rows with columnA = a. When you join to this table on that column, with that value, you will always get 2 rows. This will happen whether you use an inner join or left join.

-George

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

 

[davism]

Right, but with this:

SELECT * from TABLE C
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=40
AND TableA.ColumnB IS NOT NULL

It takes that out and brings it to 1.

SELECT * from TABLE C
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=30
AND TableA.ColumnB IS NOT NULL

I'm really NEVER using the TableA so the join would do nothing, correct? That and the "AND TableA.ColumnB IS NOT NULL" would not be returning anything especially if I'm not getting anything from TableA.

 

[gmmastros]

I'm really NEVER using the TableA

I don't understand. You include TableA in your join. What do you mean about "NEVER really using it"? Under what situation would you be using TableA?

-George

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

 

[davism]

It is a LEFT JOIN and nothing will match. So, I would expect it be nulls.

What I'm trying to do is get:

SELECT * from TABLE C
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=30

On that SQL I want it to pull:

Offer NULL (or whatever value I have albeit I did say NULL)

 

[davism]

But I am looking for just one SQL query (as this information will be in an SP)

I want the one that would just give me the row for the 42.

But I want to use the same query for the one as just mentioned that will give me like the:

Offer NULL

 

[davism]

Ok, actually able to put this sample in SQL Express and confirm what I was saying.

The first query:

SELECT * from TABLEC
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=40 AND TableA.ColumnB IS NOT NULL

This returned:

ColumnA ColumnB ColumnA ColumnB ColumnA ColumnB
40 Service Service a a 42

The second query:

SELECT * from TABLEC
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA=30 AND TableA.ColumnB IS NOT NULL

This return: Nothing and that is because the "AND TableA.ColumnB IS NOT NULL" as they are NULL. So if I comment out that AND; I get:

ColumnA ColumnB ColumnA ColumnB ColumnA ColumnB
30 Offer Offer NULL NULL NULL

I want that.

If I comment out the "AND TableA.ColumnB IS NOT NULL" from the first query I get:

ColumnA ColumnB ColumnA ColumnB ColumnA ColumnB
40 Service Service a a NULL
40 Service Service a a 42

And I won't want the NULL line but I want it if the query is with the 30 for the ColumnA in TableC.

Hopefully this makes a little better sense.

Any ideas on how to achieve this? It seems relatively simple but I must be missing something.

 

[bborissov]

SELECT * from TABLEC
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA = 40 AND 
      (TableC.ColumnA = 30 OR TableA.ColumnB IS NOT NULL)
-----
SELECT * from TABLEC
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA  = 30 AND 
      (TableC.ColumnA = 30 OR TableA.ColumnB IS NOT NULL)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[davism]

The selection for the TableC.ColumnA is actually a variable.

I just used that as a example; view it as:

SELECT * from TABLEC
LEFT JOIN TableB ON TableB.ColumnA = TableC.ColumnB
LEFT JOIN TableA on TableB.ColumnB = TableA.ColumnA
WHERE TableC.ColumnA = @RetrieveVal

That @RetrieveVal would be either a 40 or 30 or whatever else value in TableC.