SQL 'NOT IN' problems

[jmsanson]

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

[techgnome]

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

[jmsanson]

thanks for your help

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

thankyou...

Jamie

[si_the_geek]

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"

[CornedBee]

Find out your MySQL version, 4+ supports inner queries.

[The Hobo]

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)?

[CornedBee]

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").

[The Hobo]

Alright, thanks.