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

 

[gmmastros]

Quote (from an earlier post)
[tt][blue]
Here is a sample.

Table A
column a column b
[!]a[/!] NULL
b NULL
c NULL
[!]a[/!] 42
[/blue][/tt]

There are 2 rows with a ColumnA value of A, which is why you are getting 2 rows in your output. You prefer to get just one row, the one with the value. But.... What if there were more than 2 rows in TableA with the same ColumnA value. What if your TableA looked like this:

[tt][blue]
Table A
column a column b
a NULL
b NULL
c NULL
a 42
a 18
a 90
[/blue][/tt]

Would you still want just one of those rows? If so, which one?

This can be accomplished by using a derived table and an aggregate. For example, we could return the Max columnB value, the min value, the Avg, the SUM, etc.... This would allow us to return just one row.

Take a look at this example:

Declare @TableA Table(columnA Char(1), columnB Int)
Insert Into @TableA Values('a',NULL)
Insert Into @TableA Values('b',NULL)
Insert Into @TableA Values('c',NULL)
Insert Into @TableA Values('a',42)

Declare @TableB Table(columnA VarChar(20), columnB Char(1))
Insert Into @TableB Values('List'   ,NULL)
Insert Into @TableB Values('Sample' ,'b')
Insert Into @TableB Values('Offer'  ,NULL)
Insert Into @TableB Values('Service','a')

Declare @TableC Table(columnA Int, columnB VarChar(20))
Insert Into @TableC Values(10,NULL)
Insert Into @TableC Values(20,'Sample')
Insert Into @TableC Values(30,'Offer')
Insert Into @TableC Values(40,'Service')

SELECT * 
from   @TABLEC TableC
       LEFT JOIN @TableB TableB 
         ON TableB.ColumnA = TableC.ColumnB
       LEFT JOIN (
         Select ColumnA, Max(ColumnB) As ColumnBValue
         From   @TableA
         Group By ColumnA ) As TableA 
         on TableB.ColumnB = TableA.ColumnA
WHERE  TableC.ColumnA=40

Notice that I create 3 table variables and hard code your data. This is just to test the query. Assuming this code works the way you want it to, remove the table variable stuff and modify the query to use your real tables instead of the table variables.

If this does not produce the output you expect it to, let me know.


-George

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