Subqueries with Php + Mysql

vagelisp64

I have this problem :

Two joined tables.
I want to make a select statement from both tables and hold the value(s)of one field.Then a second query selecting all the record(s)except those have value the variable i kept before. How can i do this with php?

matt1

Something like

SELECT * from table2 WHERE column IN (SELECT column FROM table1) ?

vagelisp64

I think that it's not working in mysql...

matt1

Check http://www.mysql.com/doc/A/N/ANSI_diff_Sub-selects.html for the mysql syntax. It will tell you what the equivalent is.

ulixu

table1 , table2. you will get from here column (suppose it is int).

<?
mysql("Database","create temporary table temp ( column int , key(column))");
mysql("Database","insert into temp (column) select column from table1");
mysql("Database","insert into temp (column) select column from table2");
mysql("Database","select column from temp,table3 where table3.column not like temp.column");
?>
ugly?

matt1

SELECT table1.* FROM table1,table2 WHERE table1.id=table2.id is nicer.

This will show everything that is in table1 and not in table2.

As a general advice, avoid temporary tables and "flags" when you can!

matt1

Oh and you will get duplicate key errors in your example, Ulici.

ulixu

mysql("Database","create temporary table temp ( column int not null, primary key(column))"); and will be without duplicates

Anon

eg
For this select statement:select bookings.id,rooms.id,bookings.datefrom,bookings.dateto from rooms,bookings where bookings.id = rooms.id and '$date1' between datefrom and dateto.
Ok i want to hold the variable rooms.id and then to execute a second query which will exclude all the records which are between the booked dates.

vagelisp64

matt1

Hi,

it's not a good idea to make a primary key allow duplicates, that defeats the goal of the primary key :o)

matt1

Well if you want two results, do two queries. One like the one you showed, the second one where $date1 not between ..

If you want 1 result, just do $date1 not between.

[deleted]

"This will show everything that is in table1 and not in table2"

No, it will whos everything that IS in table1 AND in table2.

[deleted]

So you want to select all records where foo=bar, and then select all records where foo<>bar? why don't you just run two queries and enter exactly what you want?:

select bookings.id, rooms.id, bookings.datefrom, bookings.dateto
from rooms, bookings
where bookings.id=rooms.id
and '$date1' between datefrom and dateto

and

select bookings.id, rooms.id, bookings.datefrom, bookings.dateto
from rooms, bookings
where bookings.id=rooms.id
and NOT ('$date1' between datefrom and dateto)

matt1

ooosp!

That will show everything that is in table2 AND table1

THanks for spotting my mistake, vincent!

vagelisp64

Ok but if i have two records with id 1
and then i make a request like : i want a room(id) where '$datefrom' not between datefrm and dateto and in my table i have two records with the same id and one of them is between '$datefrom' it's ok. But what if the other is not between?. I don't want to select the record with this id...

[deleted]

"Ok but if i have two records with id 1 "

Then you have a serious inconsistency in your data. :-)

I think it would be good if you gave an example of exactly what you are looking for.

vagelisp64

Ok...
I have two tables.
i.Rooms : id,Numberofrooms,etc.....

ii. Bookings : bid,datefrom,dateto,rid (roomid),etc...

The user enter a date and i am trying to retrieve all the rooms which are not booked these dates...

So in the table of bookings i store all the records...Say that the 1st record is : 1,1/8/2001,8/8/2001,1(which is room rid (rid))
Then i store a second record 2,15/1/2001,23/1/2001,1 (again)

Then the user tries to find a room between 1/8/2001 and 8/8/2001.

Result???? The query i am trying to exetute retrieves alla the rid where are not between these dates...Ok with te first record...But what about the second???Is not between these dates but it is the same room....
Any advice???

[deleted]

That's why I suggested two queries, the first will fetch all rows where the date is between the two dates in the bookings,
and the other will select all records where the date is not between the two dates in the bookings.

vagelisp64

Ok..
How about an example???