Join SQL Query

[cheviac]

I have two tables: phone_log -- stores incoming phone calls; arc_phone_log -- archives phone calls. This is for an emergency utility outage application. The phone calls are based on feeder number which is the utility line that the caller is located at. This application is in VB6 and the database is Access. The phone calls are not individually archived but are automatically archived by feeder number when power for a line is restored. When the phone calls for a feeder is archived if any duplicate records exists in arc_phone_log I do not want the duplicates archived but I want all other calls for that feeder to be archived. I am trying to do this with an SQL query but I can't not get any of the SQL queries that I have tried to work. What I really need is an SQL statement that retrieves the data in a left join minus the data from an inner join. Thanks for any help.

 

[PHV]

an SQL statement that retrieves the data in a left join minus the data from an inner join
Something like this ?
SELECT A.*
FROM TableA AS A LEFT JOIN TableB AS B ON A.someField=B.SomeField
WHERE B.SomeField Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[cheviac]

It worked great. Thanks for the help.

 

[cheviac]

I noticed after trying the query that you gave me that it retrieves the data in a left join minus the data from the inner join but I also need the query to retrieve records in the phone_log file that does not exists in the arc_phone_log file for a particular feeder number. Thanks in advance for any help.

 

[alvechurchdata]

A sub query might help.

[TT]
Select * From Phone_Log
Where Phone_Log_ID Not In
(Select Phone_Log_ID From Arc_Phone_Log)
[/TT]

There's a longer explanation with screen dumps at

http://www.alvechurchdata.co.uk/accsubquery.htm

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[cheviac]

I have tried the following but it returns 0 records for all phone_log records:

"(select " & strFieldList & " from phone_log where " & _
"(phone_log.feeder_number='" & strFeederNumber & "') " & _
"and not exists (select " & strArcFieldList & " from " & _
"arc_phone_log where (arc_phone_log.feeder_number='" & strFeederNumber & "')))

 

[cheviac]

never mind, my mistake, go it working.

 

[cheviac]

never mind, my mistake, got it working.