#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    NE Pa.
    Posts
    96
    Rep Power
    13

    How to limit EXCEPT proper return


    Im having a problem constructing a query that only returns rows from the first table when no matching set is found in the second table, a classic use of EXCEPT

    EXCEPT ... I cant seem to figure out where to put the check condition for the column Im looking for. Here's the query Im trying:

    SELECT SOS_TRANS_NO, REQ_QUANTITY, ENTRY_DATE, BID_UNIT_PRICE, BID_PART_TYPE FROM BIDDATA

    EXCEPT

    SELECT B.SOS_TRANS_NO, B.REQ_QUANTITY, B.ENTRY_DATE, B.BID_UNIT_PRICE, B.BID_PART_TYPE FROM BIDDATA AS B INNER JOIN MASTER_GOVT_CONTRACTS AS M ON (B.REQ_NUMBER = M.RFQ_NUMBER) AND B.NSN_NUMBER='4710-01-434-6369'

    My problem is I want only the instances of the nsn_number 4710-01-434-6369 that DO NOT Have contracts awarded, this query is returning all of the rows EXCEPT this nsn number, what I should be getting is 0 rows returned. Im think I need a where clause on the first query to specify the nsn number but cant seem to get it anywhere Postgres likes

    Any clues here ... Ive been staring & playing with this for hours & just cant seem to see what needs to be done ... thanks for all input!
    DC Dalton
    DCD Designs
    SCJP
  2. #2
  3. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Remember, one of the main rules of the relational model is that the result of any query is a relation. That means that you can break apart your queries into meaningful pieces and analyze those.

    Remember, EXCEPT is kind of analagous to UNION: they both just take two completely separate query results and perform one simple operation. UNION just adds the results from the second query onto the first, so the results look like one larger table.

    EXCEPT essentially "subtracts" the results of the second query from the first. So, lets think about this a little. I find that these things just fall into place of you break them down into small steps:

    1. First, you want to only deal with rows where the NSN_NUMBER is '4710-01-434-6369'. (everything else is extraneous to your needs). So, before you do anything else, make that your first query:

    SELECT SOS_TRANS_NO, REQ_QUANTITY, ENTRY_DATE, BID_UNIT_PRICE, BID_PART_TYPE FROM BIDDATA WHERE NSN_NUMBER='4710-01-434-6369';

    Now, we have just made sure that the query will never return any other nsn numbers, no matter what.

    3. Next, we want to get the list of all the NSNs that have a government contract, so we can EXCEPT them from the list. Since we have already dealt with the NSN number, all we need to do for the second query is

    SELECT B.SOS_TRANS_NO, B.REQ_QUANTITY, B.ENTRY_DATE, B.BID_UNIT_PRICE, B.BID_PART_TYPE FROM BIDDATA AS B INNER JOIN MASTER_GOVT_CONTRACTS AS M ON (B.REQ_NUMBER = M.RFQ_NUMBER);

    3. Now, first make sure that each query runs by itself and returns the results you want.

    4. Then use the EXCEPT clause:

    SELECT SOS_TRANS_NO, REQ_QUANTITY, ENTRY_DATE, BID_UNIT_PRICE, BID_PART_TYPE FROM BIDDATA WHERE NSN_NUMBER='4710-01-434-6369'

    EXCEPT

    SELECT B.SOS_TRANS_NO, B.REQ_QUANTITY, B.ENTRY_DATE, B.BID_UNIT_PRICE, B.BID_PART_TYPE FROM BIDDATA AS B INNER JOIN MASTER_GOVT_CONTRACTS AS M ON (B.REQ_NUMBER = M.RFQ_NUMBER);

    This query could be expressed in plain English as "Get all the bids with a certain NSN number", and then get a list of all bids that have a government contract, then discard any rows that are the same.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    NE Pa.
    Posts
    96
    Rep Power
    13
    That does return the results I am looking for & I see the mistake I was making, using the nsn_number in the second query. One odd thing tho, when I excecute the query via pgAminII I get this error message "Invalid procedure call or argument" then it gives me the results. I havent tried it through my program (will get to that later today) to see if that throws an sql error.
    DC Dalton
    DCD Designs
    SCJP
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    NE Pa.
    Posts
    96
    Rep Power
    13
    That error must be a pgAdmin bug because the error does not happen in the application.

    I must admit leaving the where nsn_number off of the second query doesnt seem to make much sense, I would think it would cause less work & searching on the second query by only return a possible matching row based on that nsn ... I would have never thought of that, thanks much ... now on to that crazy 5 table join I put in the other post "oh joy!"
    DC Dalton
    DCD Designs
    SCJP
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Well, The main point of what I mentioned was that the nsn_number where clause was necessary in the first query. There's no reason not to include the nsn_number in the second query also, but I doubt it will make much difference because PostgreSQL has a pretty intelligent query planner. Give it a try with EXPLAIN, and see if the query planner shows any different results.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com

IMN logo majestic logo threadwatch logo seochat tools logo