Announcement

Collapse
No announcement yet.

help creating sql statement

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • help creating sql statement

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

    Each table has two fields (F1 and F2)

    Code:
    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".

  • #2
    Select t2.f2 from t1,t2 where
    t1.f1=t2.f1
    and t2.f2 = 4
    hellobasic

    Comment


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

      Comment


      • #4
        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)
        Paul Squires
        FireFly Visual Designer (for PowerBASIC Windows 10+)
        Version 3 now available.
        http://www.planetsquires.com

        Comment


        • #5
          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?
          Last edited by Edwin Knoppert; 24 Jun 2009, 04:54 PM.
          hellobasic

          Comment


          • #6
            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....

            Comment


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

              Comment


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

                get a list of matching data:

                Code:
                        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

                Comment


                • #9
                  >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
                  Last edited by Michael Mattias; 24 Jun 2009, 06:31 PM.
                  Michael Mattias
                  Tal Systems (retired)
                  Port Washington WI USA
                  [email protected]
                  http://www.talsystems.com

                  Comment


                  • #10
                    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.

                    Code:
                    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.

                    Comment


                    • #11
                      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
                      Best regards
                      Jules
                      www.rpmarchildon.com

                      Comment


                      • #12
                        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
                        Paul Squires
                        FireFly Visual Designer (for PowerBASIC Windows 10+)
                        Version 3 now available.
                        http://www.planetsquires.com

                        Comment


                        • #13
                          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.
                          Scott Slater
                          Summit Computer Networks, Inc.
                          www.summitcn.com

                          Comment


                          • #14
                            Originally posted by Paul Squires View Post
                            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
                            Thanks,
                            Gary Stout
                            gary at sce4u dot com

                            Comment


                            • #15
                              Originally posted by Gary Stout View Post
                              ...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.
                              Paul Squires
                              FireFly Visual Designer (for PowerBASIC Windows 10+)
                              Version 3 now available.
                              http://www.planetsquires.com

                              Comment


                              • #16
                                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.
                                Last edited by Shawn Anderson; 25 Jun 2009, 11:00 AM.

                                Comment


                                • #17
                                  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.
                                  Last edited by Joseph Cote; 30 Jun 2009, 12:52 PM.
                                  The boy just ain't right.

                                  Comment


                                  • #18
                                    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.
                                    The boy just ain't right.

                                    Comment

                                    Working...
                                    X