[RESOLVED] Query for when there is no join

[nice_guy_mel]

I'm looking for the SQL syntax necessary to get back the records I need.

I have a program that works both with Oracle(9) and SQLServer databases. One of the tables holds text messages and what time they were created. We normally receive these messages via TCP communications, but recently we started storing messages that occasionally come from files. The messages that come from files have a creation time that is quite old.

A separate program connects to the database and generates reports based on creation dates. We only want this report to be for the TCP messages, not the file ones. We cannot modify the existing message table since we already have customers using it, but we can add new tables. A new table was added with just one column. It would store the record ID of any messages in the message table that arrived from a file. We didn't want to do the opposite and store the record IDs of messages that came from TCP because that would be a lot of new data. The file messages are rarely used, so for database size reasons, we want to record just the IDs of messages that came from files.

Now that the new table is added, we just have to change the SQL for the report generating program. However, I'm having trouble with the syntax. What I want is to join the message table with this new table, and select only the records in the message table that DO NOT have their IDs in the new table.

I'm more familiar with doing joins where you want to INCLUDE records that have matching IDs, not where you want to EXCLUDE records that have matching IDs.

For simplisity sake, lets use the following tables:
Messages with Columns: ID (Primary Key), Message (Text), Created (TimeDate)
FileMessages with Column: ID (Foreign Key)

The previous query would have simply been:
Select Created, Message From Messages

I'm starting with the Oracle(9) query first. I've tried doing:
1. Select Created, Message From Messages m inner join FileMessages f on m.ID <> f.ID
2. Select Created, Message From Messages m inner join FileMessages f on m.ID = f.ID where f.ID = null

Neither gets me what I'm looking for, but hopefully someone 'out there' has done something similar before. If you could post both the oracle(9) and SQLServer query (that is if they differ) that would be great.

[Edit]
Turns out #2 was close, what I needed was an 'outer' and an 'is'
Select Created, Message From Messages m full outer join FileMessages f on m.ID = f.ID where f.ID is null

Hope this helps others

[davide++]

Hi all.

If I have understod what you did, you added a table with one column that says what messages are taken from file, and you're trying to write a join on the two tables to select the message that are arrived from FTP.
Maybe a simplier solution is adding a new column to the existing table, a flag that stores if the message is arrived from file or from FTP. So, you don't need to perform join, you can write a simple query that selects by the flag.