left join structure 1

[chmdguy2]

I've been having a problem with the structure of a simple 'no-match' query. For example, I have two tables... Table 1 and Table 2. I want to pull all the records from table 1 that are not in table two.

Table 1 Table 2
Id Id

Here's what I've written, but I'm a bit stuck..
--------------------------------
select distinct
Table1.Id,
Table2.Id
from Table1
Table2
left join Table1 on Table2.Id = Table1.Id
where Table2.ID is null;
-------------------------------------

I'm obviously doing something wrong... Can someone please help! Thanks so much!

 

[r937]

yeah, you don't use both methods (listing the tables and using JOIN syntax)

just JOIN them

[tt]select distinct
Table1.Id
, Table2.Id
from Table1
left outer
join Table2
on Table1.Id = Table2.Id
where Table2.ID is null[/tt]

if Table1.Id is the primary key, you don't really need DISTINCT

and you don't need Table2.Id in the SELECT list, because you know it's gonna be null

;-)


rudy

http://r937.com/

 

[chmdguy2]

Thanks Rudy! Have a great holiday!

 

[snag56]

There are fundamentally 2 ways to accomplish this,
and it looks like you have attempted to use both approaches at once. The preferred (newer) method is :

select T1.*
from Table1 T1
left outer join Table2 T2
on T1.[ID] = T2.[ID]
where T1.[ID] is not null
and T2.[ID] is null

The older, Oracle-like method is :

select T1.*
from Table1 T1, Table2 T2
where T1.[ID] *= T2.[ID]

 

[r937]

i'm surprised to see you describe the older microsoft outer join syntax as "oracle-like"

in sql server, it's [tt]T1.[ID] *= T2.[ID][/tt]

in oracle, it's [tt]T1.[ID](+) = T2.[ID][/tt]


rudy

 

[JohnDTampaBay]

I believe that using EXISTS is a little more efficient as processing stops as soon as a matching row is found.

SELECT DISTINCT t1.Id
FROM Table1 t1
WHERE NOT EXISTS(SELECT 1 FROM Table2 t2
                 WHERE t1.Id = t2.Id)

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.

 

[r937]

ordinarily, yes, angel, that is correct, except that the example you give is a correlated subquery, which is usually executed by the optimizer as a join anyway

the true test would be to take the real life case, code it both ways, and do an EXPLAIN

;-)

 

[rcollier5]

I have a question related to this problem:

I have a table with weekending dates and facilities that have submitted reports

I also have a table listing every facility there is.

is there an SQL statement where i can create a query listing what facilites have not submitted reports for each weekending date?


it seems to be like a no_match query, but specific to the weekending column rather than an entire table

Thanks if you can help!

 

[r937]

yes, join the facility table to the reports table, with a WHERE condition for the range of weekending dates you're interested in, GROUP BY facility, and then say

HAVING COUNT(DISTINCT weekendingdate) < somenumber

where the number is how many weekending dates are in the range specified


rudy

 

[rcollier5]

Thanks,I think I understand, but can you give me an example of an entire statement, cuz im a rookie, and it still isnt working properly....heres a beter picture of what my tables look like

ActivityWeekly
---------------
Weekending | Facility | ect
____________________________
*all columns may include multiple entires of same date & facitliy


Facilities
------------
Facility | ect


I'd like to use all dates that are in the database instead of counting and specifing

thanks so much for your help

 

[r937]

[tt]select Facility
from Facilities
inner
join ActivityWeekly
on Facilities.Facility
= ActivityWeekly.Facility
group
by Facility
having count(distinct Weekending)
< ( select count(distinct Weekending)
from ActivityWeekly ) [/tt]

 

[rcollier5]

Thanks I really apreciate all the help, but i am getting a syntax error you see any reason why...i dont

 

[r937]

no, i don't either

give me a hint

what was the error?

 

[rcollier5]

It gave me a snytax error starting at the fist count,

BTW im using SQL view in MS Access...does it support all functionalities of SQL?

Ryan

 

[r937]

access does not support COUNT DISTINCT

or, at least it didn't in access 97, not sure about later versions