Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27

    Unanswered: Access Query - SQL MINUS help

    Greetings DBForum Users,

    I have three cascading tables: Company--->Proposal--->Contract. One company can receive many proposals and each proposal can generate many contracts. I need to list all the Proposals that are still pending, that is all Proposals that have not yet become a Contract. It looks like I need to use the MINUS function where it would take all the Proposals and MINUS the ones listed in the Contracts leaving all the pending Proposals. When I tried a SQL MINUS statement but kept getting errors.

    *******************************************

    TABLES GIVEN:
    Company
    CompanyID.....Name
    1..................Georgia Pacific
    2..................International Paper
    3..................Boise Cascade

    Proposal
    ProposalID.......CompanyID
    P1000.............1
    P1001.............2
    P1002.............3
    P1003.............1
    P1004.............2

    Contract
    ContractID......ProposalID
    C1000............P1001
    C1001............P1000
    C1002............P1002
    C1003............P1001

    *******************************************

    NEEDED OUTPUT
    Name......................ProposalID
    Georgia Pacific..........P1003
    International Paper....P1004

    *******************************************

    This is the MINUS statement I have been working with. Each Select statement works by itself, however I get an error when I put it all together.

    SELECT company.name, proposal.proposalID
    FROM company, proposal
    WHERE company.companyID = proposal.companyID
    MINUS
    SELECT company.name, proposal.proposalID
    FROM company, proposal, contract
    WHERE proposal.proposalID = contract.proposalID
    AND company.companyID = proposal.companyID


    Thanks In Advance....
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2004
    Location
    Vancouver, WA
    Posts
    41
    One option would be to try a subquery:

    Code:
    SELECT company.name, proposal.proposalID
    FROM company, proposal
    WHERE company.companyID = proposal.companyID
    AND proposalID NOT IN (
       SELECT proposal.proposalID
       FROM proposal, contract
       WHERE proposal.proposalID = contract.proposalID
    )
    I didn't double-check this with a test, so the criteria may be incorrect, but perhaps you get the idea.

  3. #3
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27

    Good Stuff

    That Worked....Thanks for the quick reply.

    Still, it seems like the MINUS statement should have worked.

    Thanks Again!

  4. #4
    Join Date
    Jan 2004
    Location
    Vancouver, WA
    Posts
    41
    MINUS is not listed in the SQL Server 2000 Books Online, so it is possible (?) that MS does not fully support it.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    whether MINUS is in SQL Server Books Online would seem to have little bearing on whether or not Microsoft Access supports it...

    i like the NOT IN, but it might not be the most efficient

    another way to do it is with NOT EXISTS using a correlated subquery

    the easiest way, in my opinion, is to use a left outer join and check for nulls in the right table key (which signify unmatched rows):
    Code:
    select company.name
         , proposal.proposalID
      from company
    inner
      join proposal
        on company.companyID 
         = proposal.companyID
    left outer
      join contract   
        on proposal.proposalID 
         = contract.proposalID
     where contract.proposalID is null
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jan 2004
    Location
    Vancouver, WA
    Posts
    41
    Just making an assumption, since Access seems to support at least a subset of SQL Server's T-SQL and at most all of SQL Server's T-SQL. If SQL Server doesn't support a T-SQL statement, I doubt Access will.

    But we all know assumptions can be bad...

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oracle supports a subset of microsoft's t-sql

    so does mysql

    i wouldn't use BOL for either one of those databases

    you know access is a totally different animal than sql server, right?

    wildcards: LIKE '*str*' versus LIKE '%str%'

    dates: = #2004-07-22# versus = '2004-07-22'

    date functions: DATEADD("d",1,foo) versus DATEADD(d,1,foo)

    et friggin cetera

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2003
    Location
    Knoxville, TN - USA
    Posts
    27
    I vote for the "NOT IN" method. For a rookie, such as myself, it seems more intuitive. Maybe this is due to the fact that I have spent the necessary time to understand the “JOIN” techniques (Left, Right, Inner, Outer).

    Thanks for everyones input...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •