Results 1 to 8 of 8

Thread: SQL 'NOT IN' problems

  1. Jan 29th, 2004, 05:51 PM #1
    jmsanson
    jmsanson is offline

    Thread Starter
    New Member
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    5

    SQL 'NOT IN' problems

    Hi all...

    I have to tables. One has a set of dates, the other a list of transactions. I want to be able to produce a set of dates which are not included in the transaction table based on an ID.

    I have tried this

    "SELECT date FROM SchoolDates NOT IN (SELECT date FROM Transactions WHERE ID='$id') ORDER BY date"

    i have tried various combinations of the above but none seem to work, i am using php and a mySQL db.

    can anyone help?

    Thanks

    Jamie
    Reply With Quote Reply With Quote
  2. Jan 29th, 2004, 06:12 PM #2
    techgnome
    techgnome is offline
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,687
    I don't think mySQL supports inner select queries like that (at least not yet)....
    You may need to do the inner select first, build the list of ID's then pass that back to the outter select ... but I could be wrong.

    TG
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *
    Reply With Quote Reply With Quote
  3. Jan 29th, 2004, 06:30 PM #3
    jmsanson
    jmsanson is offline

    Thread Starter
    New Member
    Join Date
    Sep 2001
    Location
    New Zealand
    Posts
    5
    thanks for your help

    my SQL isn't that strong, how would you do what you've suggested.

    thankyou...

    Jamie
    Reply With Quote Reply With Quote
  4. Jan 30th, 2004, 05:52 AM #4
    si_the_geek
    si_the_geek is offline
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,974

    Re: SQL 'NOT IN' problems

    i dont know about mySQL, but for any other database your syntax is wrong, it should be:


    "SELECT date FROM SchoolDates WHERE date NOT IN (SELECT date FROM Transactions WHERE ID='$id') ORDER BY date"
    (Microsoft MVP from July 2007 to June 2017) . . . . . . . . . . Hitchhiker's Guide to Getting Help at VBForums

    Database Development FAQs/Tutorials (updated May 2011) (includes fixing common DB related errors, and [VB.Net] ADO.Net Tutorial, and [Classic VB] ADO tutorial /further steps)
    other useful DB bits: . Connection strings (alternative copy).•. MDAC/Jet/ACE downloads .•. SQL Server downloads .

    Classic VB FAQs (updated Oct 2010) (includes fixing common VB errors)
    some of my Classic VB bits: . Tutorial: How to automate Excel from VB6 (or VB5/VBA) .•. SQL 'Select' statement formatter/checker .•. Convert colour number to colour name .•. FlexGrid: fill from recordset .•. FlexGrid: AutoSize columns .•. DB Reserved Words checker .
    Reply With Quote Reply With Quote
  6. Jan 30th, 2004, 09:54 AM #5
    CornedBee
    CornedBee is offline
    Kitten CornedBee's Avatar
    Join Date
    Aug 2001
    Location
    In a microchip!
    Posts
    11,594
    Find out your MySQL version, 4+ supports inner queries.
    All the buzzt
    CornedBee

    "Writing specifications is like writing a novel. Writing code is like writing poetry."
    - Anonymous, published by Raymond Chen

    Don't PM me with your problems, I scan most of the forums daily. If you do PM me, I will not answer your question.
    Reply With Quote Reply With Quote
  7. Feb 4th, 2004, 11:21 AM #6
    The Hobo
    The Hobo is offline
    Stuck in the 80s The Hobo's Avatar
    Join Date
    Jul 2001
    Location
    Michigan
    Posts
    7,256
    Does anyone here use MySQL 4? My host upgrade to it awhile ago, but a great number of their clients had problems getting scripts to run afterwards, so they degraded back to a 3 release.

    Has anyone else had problems with it?

    And as a side question, can I have MySQL 3 and 4 installed at the same time, and use them one at a time (for development and testing)?
    My evil laugh has a squeak in it.

    kristopherwilson.com
    Reply With Quote Reply With Quote
  8. Feb 5th, 2004, 03:42 AM #7
    CornedBee
    CornedBee is offline
    Kitten CornedBee's Avatar
    Join Date
    Aug 2001
    Location
    In a microchip!
    Posts
    11,594
    Does anyone here use MySQL 4?
    I installed it on my new server. But that doesn't mean anything because I haven't written any scripts for it.
    My brother uses it though, I believe. And since my old server still runs 3, that means my scripts are perfectly compatible with both.

    And as a side question, can I have MySQL 3 and 4 installed at the same time, and use them one at a time (for development and testing)?
    Yes, but you need to set one to use a different port than the default (which is somewhere in the 4000 range I think). This means you can't use simply the hostname to connect ("localhost") but you also have to pass the port ("localhost:4999").
    All the buzzt
    CornedBee

    "Writing specifications is like writing a novel. Writing code is like writing poetry."
    - Anonymous, published by Raymond Chen

    Don't PM me with your problems, I scan most of the forums daily. If you do PM me, I will not answer your question.
    Reply With Quote Reply With Quote
  9. Feb 5th, 2004, 09:18 AM #8
    The Hobo
    The Hobo is offline
    Stuck in the 80s The Hobo's Avatar
    Join Date
    Jul 2001
    Location
    Michigan
    Posts
    7,256
    Alright, thanks.
    My evil laugh has a squeak in it.

    kristopherwilson.com
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

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

Forum Rules


Click Here to Expand Forum to Full Width