SQL join

bruceg

I would like to write a SELECT statement on a ODBC datasource, to select something like the following:

table1 table2

id id
desc desc

select all records from table1, and then compare to table2. If there is an id in table1 but not table2, return the row.

It seems like it would be fairly simple, and I have tried equal joins, left outer joins, and it keeps giving me records that do exist in table2. Any help is appreciated.

sarahk

select * from table1 where id not in (select id from table2)

that should work.

bruceg

Hi Sarah -

I gave that a try, and got no records. I'm beginning to wonder if this is an ODBC driver issue. Other queries to this datasource seem to work ok, but I'm having a heck of a time trying to get this query to work.

bruceg

I should also mention that 'id' in table2 is not a primary key or unique. In fact, there are duplicate 'id' numbers in this table. I'm not sure if that matters, because if it does not exist, then it should be returned as a result set.

amesowe

Im not sure but give it a try
<code>
select table1.id from table1 where id not in (select table2.id from table2)
</code>

amesowe

im sorry i missed sumthn out.
heres the new one:

select table1.id from table1 where table1.id not in (select table2.id from table2)

matt1

Try this:

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.id IS NULL;

saltwater

How about

select table1.* from table1 LEFT JOIN table2 ON table1.id=table2.id where table2.id is NULL;

bruceg

nope - still get everything in the right table.

bruceg

no go - that was my first select statement. The logic is there, but it does not work. I'm suspecting an ODBC driver bug at this point. If you can think of any other statements that might work, please post them, and I'll give it a whirl.

saltwater

Take a look at the join syntax, I just started with it today, so I'm a newbie too 🙂

http://www.mysql.com/doc/J/O/JOIN.html

bruceg

Well, this is an ODBC datasource (Informix database) so I don't think the MySQL rules apply here. Informix is SQL92 compliant, wheras MySQL is not.

Thanks for the link, though - it should come in handy.

Anon

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.id IS NULL;

Anon

Hi,

try it on your database. If it works then it's a problem with ODBC.

If it works on the database would go to ODBC TEST and test the query.

If it works, then blame Access.
If it doesn't, blame ODBC.

Anon

Maybe there are no unmatched records in table 2???

If all the ids in table2 exist in table1 then you'd get no results.

Are you sure there are unmatched rows in table2?

bruceg

that one was already mention, thx though..

bruceg

I ran a seperate query that displayed all rows in table2. the table2.id is duplicated, wheras table.id is unique. for example:

table1.id table2.id

1 1
2 1
3 2
4 3

my statement, should return id#4 as not being in table2.

with all the different statements given to me, either I get 1 result, or none, or all from table2.

I ran this same query in Crystal Reports, and using an option to have Crystal remove the records where there is not a value in table2, where there is in table1, I get the proper results. As soon as I try and have the server try and do this, it gets messy. Crystal reports actually brings in all records from each table, then based on my requriement, removes the records.

Maybe I can get PHP to do this? This must be an ODBC driver issue, because all the logic in the statements seem correct.

Anon

Here is my test:

select * from table1;

ID

--- 4 row(s) selected.

select * from table2;

ID

--- 4 row(s) selected.

select * from table1 left join table2 on table1.id=table2.id where table2.id i
s null;

ID ID

 4       ?

--- 1 row(s) selected.

You must have a problem with your ODBC driver.

bruceg

that's what i'm thinking. i'm trying to access the db directly on that machine, but there are very little user tools available, and I don't wish to write a full blown C program to do it. I'm contacting support.

bruceg

Got it working:

select * from table1 where id > -1 AND id NOT IN (select UNIQUE id from table2 where id > -1)

That gives me what I need. Looks like ODBC is fine.