Join??????

3 Aug 2006 3:43 PM

Curtis

Select    Distinct t1.ClientID, t1.ScanJobHeaderID, t1.SearchEngineGroupID
    From    dbo.table1 t1
        INNER JOIN
            dbo.table2 t2 on (t1.ScanJobHeaderID != t2.ScanJobHeaderID)
    Order by t1.ScanJobHeaderID

I only want the rows from table1 where they don't equal any of the rows from
table2. My statement works as long as there is only one row in table2. WHY?

3 Aug 2006 3:51 PM

Daniel Crichton

Curtis wrote on Thu, 3 Aug 2006 08:43:02 -0700:

> Select Distinct t1.ClientID, t1.ScanJobHeaderID, t1.SearchEngineGroupID
> From dbo.table1 t1
> INNER JOIN
> dbo.table2 t2 on (t1.ScanJobHeaderID != t2.ScanJobHeaderID)
> Order by t1.ScanJobHeaderID
>
> I only want the rows from table1 where they don't equal any of the rows
> from table2. My statement works as long as there is only one row in
> table2. WHY?

The INNER JOIN will match on the row where ScanJobHeaderID doesn't match. If
there are no, there's nothing to match on!

Try:

Select Distinct t1.ClientID, t1.ScanJobHeaderID, t1.SearchEngineGroupID
From dbo.table1 t1
LEFT JOIN
dbo.table2 t2 ON t1.ScanJobHeaderID = t2.ScanJobHeaderID
WHERE t2.ScanJobHeaderID is null
Order by t1.ScanJobHeaderID

You could also look into using NOT EXISTS.

Dan

3 Aug 2006 3:54 PM

Tav

Curtis wrote:

> Select    Distinct t1.ClientID, t1.ScanJobHeaderID, t1.SearchEngineGroupID
>     From    dbo.table1 t1
>         INNER JOIN
>             dbo.table2 t2 on (t1.ScanJobHeaderID != t2.ScanJobHeaderID)
>     Order by t1.ScanJobHeaderID
>
> I only want the rows from table1 where they don't equal any of the rows from
> table2. My statement works as long as there is only one row in table2. WHY?

Hi Curtis,

Is there any reason for using 'DISTINCT'?

In T-SQL use '<>' for not equal to and not '!='.

It makes no different (at least I think it doesn't) to use '<>' join or
a LEFT JOIN with a check for NULL. However, it is better programming
practice to use the latter. see:

SELECT        t1.ClientID,
        t1.ScanJonHeaderID,
        t1.SearchEngineGroupID
FROM        dbo.table1 t1
LEFT JOIN    dbo.table2 t2 ON t1.ScanJobHeaderID = t2.ScanJobHeaderID
WHERE        t2.ScanJobHeaderID IS NULL

Regards,

    -Tav.-

Tavis Pitt

3 Aug 2006 4:34 PM

Curtis

Thank you both! It wasn't clear why != wasn't working.

Show quote

"Tav" wrote:

>
> Curtis wrote:
> > Select    Distinct t1.ClientID, t1.ScanJobHeaderID, t1.SearchEngineGroupID
> >     From    dbo.table1 t1
> >         INNER JOIN
> >             dbo.table2 t2 on (t1.ScanJobHeaderID != t2.ScanJobHeaderID)
> >     Order by t1.ScanJobHeaderID
> >
> > I only want the rows from table1 where they don't equal any of the rows from
> > table2. My statement works as long as there is only one row in table2. WHY?
>
> Hi Curtis,
>
> Is there any reason for using 'DISTINCT'?
>
> In T-SQL use '<>' for not equal to and not '!='.
>
> It makes no different (at least I think it doesn't) to use '<>' join or
> a LEFT JOIN with a check for NULL. However, it is better programming
> practice to use the latter. see:
>
> SELECT        t1.ClientID,
>         t1.ScanJonHeaderID,
>         t1.SearchEngineGroupID
> FROM        dbo.table1 t1
> LEFT JOIN    dbo.table2 t2 ON t1.ScanJobHeaderID = t2.ScanJobHeaderID
> WHERE        t2.ScanJobHeaderID IS NULL
>
> Regards,
>
>     -Tav.-
>
> Tavis Pitt
>
>

3 Aug 2006 5:24 PM

Omnibuzz

Hi Curtis
Let me try to explain..
say you have 2 tables tbl1(col1) and tbl2(col2) each with 3 rows
tbl1    tbl2
col1   col2
1          1
2          2
3          3

now take this query (very similar to what you had given)

select tbl1.col1, tbl2.col2
from tbl1, tbl2
where col1 != col2

Taking it one step at a time.. the result of the following is (you can check
it)

select tbl1.col1, tbl2.col2
from tbl1, tbl2

col1 col2
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3

of the above result if I apply the filter col1 != col2 then the following
rows will be filtered
1 1
2 2
3 3
But you will get the rest 6.. and you should be able to understand why :)

Hope I made sense..
you can check this link for using "not in" and "not exists" queries
http://omnibuzz-sql.blogspot.com/2006/06/understanding-and-using-not-operator.html

Hope this helps

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/