help creating sql statement

[Shawn Anderson]

I have two tables.
Let's call them T1 and T2.

Each table has two fields (F1 and F2)

T1                       T2
F1    F2                F1   F2
a     1                  a     1
a     2                  a     2
a     3                  a     3
                          a     4

I want a single sql statement that will find
1) F1 is the same in both tables
2) values from T2 that do not exist in T1

I'm trying this:
SELECT T2.F2 FROM T1,T2 WHERE
T1.F1=T2.F1
and T1.F2 <> T2.F2

but that gives me everything.

I want the answer of "4".

[Edwin Knoppert]

Select t2.f2 from t1,t2 where
t1.f1=t2.f1
and t2.f2 = 4

[Shawn Anderson]

I wouldn't know the value of fields.
I just showed 4 as an example of the result of a correct query.

[Paul Squires]

Maybe something like:

SELECT F1, F2 FROM T2 MINUS SELECT F1, F2 FROM T2

.. or maybe:

SELECT F1, F2 FROM T2 EXCEPT SELECT F1, F2 FROM T2

(reference: http://www.1keydata.com/sql/sql-minus.html)

[Edwin Knoppert]

That would be a left outer join and test for null, something like:

SELECT F2
FROM
(
Select t1.f2 As f1, t2.f2
from t1
LEFT OUTER JOIN t2 on T2.F1=T1.F1
) AS TTT
WHERE f1 IS NULL

Hmm, i am not a DB wizard, we have a few at the office though.
what db engine are you using?

[Shawn Anderson]

I'm using MySql 4.1.
I've got some ideas here. I'll work with it and post my results if I get it to work....

[Edwin Knoppert]

By accident i compared tables but you should compare the fields, see above, they are bold now.

[Shawn Anderson]

Though it probably can be done with one statement, I used two.

get a list of matching data:

        sqlStatement="Select sampleID From anaData where motility='not checked' and sampleID in "+_
        "("+_
        "Select motility.sampleID From motility,sampleData,anaData "+_
        "where motility.subID="+sqlPad(subID)+"  "+_
        "And motility.subID=sampleData.subID  "+_
        "And motility.sampleID=sampleData.sampleID "+_
        "And motility.subID=anaData.subID  "+_
        "And anaData.sampleID=motility.sampleID "+_
        ")"

followed by a delete for the match

[Michael Mattias]

>Though it probably can be done with one statement, I used two.

Um, I think you DID do it with one statement.

Subqueries don't count; the DBMS manager (ODBC? OLE? Native? Code not shown) will create a single plan from the one statement. A good DBMS will optimize it for you.

That one statement can then be executed as many times as you want without reparsing/replanning (assuming you PREPARE it). (really only useful if you have a replaceable parameter, which it appears you don't).




MCM

[Peter Jinks]

FWIW this works for me. I'm using SQL Server though, so I'm not sure if this exact syntax can be used in MySQL.

SELECT T2.F1, T2.F2 
FROM T2
LEFT OUTER JOIN T1
ON T1.F1 = T2.F1 AND T1.F2 = T2.F2
WHERE T1.F1 Is Null

Regards,

Pete.

[Jules Marchildon]

Not to distract, but wanted to thank Paul for the reference link above, I just spent 20 mintues clicking away to learn a few things about SQL. Lots to figure out... I'm a DB rookie, just starting to learn the basics, playing with SQLite, and your SQLightning. Good to know there is very good SQL-knowledge base out there.

Thx.
Regards,
Jules

[Paul Squires]

Thanks Jules, you're very welcome!

SQLitening has come a long way in a short time. It is a pretty powerful database that works perfectly in both local and/or client/server modes. I can't see myself using anything else for the type of apps that I create.

Here is a link to probably one of the more popular SQL learning sites: http://www.w3schools.com/Sql/default.asp

[Scott Slater]

You can also download SQLite.exe from the main SQLite website. It is a command line utility that you can type SQL commands directly into, and see the results right there. It is a good learning tool, when trying to piece together SQL statements.

[Gary Stout]

Here is a link to probably one of the more popular SQL learning sites: http://www.w3schools.com/Sql/default.asp

Paul,

Thanks for the learning site! It looks very helpful for my "flat file" mind

[Paul Squires]

...helpful for my "flat file" mind

Yeah, we have to get you out of that mindset.

It took me a long time to finally make the jump to SQL. I always figured that the SQL engines were too slow and bloated (ie. DAO) but having found and worked with SQLite I realize that the benfits of SQL make database programming a little easier. I never wanted to have to install huge engines like SQL Server or MySQL just to run a simple desktop app. With Sqlite it is a pleasure to write database apps.

[Shawn Anderson]

It's true, once you start using SQL you won't go back. I'm still learning the more complex SQL commands but you can learn all of the basics in a few hours. I have a great book "Teach Yourself SQL in 10 minutes" by Ben Forta that is a great resource.

MySql serves me in this particular situation because this is a web application with lots of users. Also, when using MySql you can directly access a database out on the Internet, which is makes it relatively easy for people at many different locations to be working on the same database. MySql is a more robust database than SQLite that can handle thousands of simultaneous users. We have a stand-alone SQL server (Linux) that handles thousands of tables and millions of transactions daily. Once the server is set up, it's no big deal to add new database for a particular program so I end up using it a lot.

Having said that, SQLite is great for desktop or workgroup applications and certainly is the better choice for some applications.

I also would sniff at using a Microsoft Access MDB file. I've used them many times. Usually I'll use a PB front end and Access for reports. It is a time saver.

[Joseph Cote]

What I would write in DB2

SELECT F2
FROM T2
WHERE NOT EXISTS
( SELECT 'EXISTS'
FROM T1
WHERE T1.F2 = T2.F2
)

Have not tried this, but it looks like a good first try to me.

Explanation of how this works: Database engine will look through all rows of T2, and recalculate the subselect because it is a correlated subselect (something in the subselect is matched to something outside of it) for each row. If any T1.F2 matches the given T2.F2 it will come back with 'EXISTS', the NOT will negate it, and the current row of T2 will be discarded. If no rows of T1 match it comes back with NULL, the NOT EXISTS makes it a TRUE value and the current value of T2.F2 will be selected into the result set.


A different approach, and perhaps more efficient unless the optimizer is really really smart....

SELECT F2
FROM T2
WHERE F2 NOT IN (
SELECT DISTINCT F2
FROM T1)

In this case the subselect runs one time, and creates a result set of each value found from all the T1.F2's. The DISTINCT part means to just select the individual value once and dicard duplicates. Then the outside select will run testing each row of T2. If the T2.F2 value is NOT IN the list created from the T1 data it will be selected for the output set.

[Joseph Cote]

Whoops! Missed the part about F1's being equal...

Let's try this.

SELECT T2.F2
FROM T2
WHERE T2.F2 NOT IN (
SELECT DISTINCT T1.F2
FROM T1
WHERE T1.F1 = T2.F1)

Correlated subselect again. For each row of T2, the subselect will be recalculated. It will make a list of all F2 values in the T1 table where T1.F1 matches the current T2.F1. Then the outer select will see if the current row's F2 value is among those found in T1 or not, selecting the ones that are NOT.