Query Help Needed!

Anon

$allquery = "select * from building,taken where building.ID=taken.buildingID AND ((('$fromdate' < taken.from) AND ('$todate' < taken.from)) OR (('$fromdate' > taken.to) AND ('$todate' > taken.to))) GROUP BY bezet.buildingID order by building.ID LIMIT ".$l1.",3";

There can be multiple records in the taken table and that gives me a problem!
If there are multiple records and find one record that doesnt match for the date checks he uses the building but I don't want it to be used. If there is one record in taken that doesnt match for the date checks the building should not be selected

I realy need some help cause I'm going crazy on this !

greetz,
Neutje
http://www.neutje.be

tomhath

If I understand what you are trying to do, this should get you the buildings that are not taken during the from/to date range. The logic is confusing because you are looking for something that is not there (that's why the WHERE checks for a NULL taken.buildingID). Your date range check was more complicated than necessary and the GROUP BY didn't do anything.

SELECT DISTINCT building.ID
FROM building
LEFT JOIN taken
ON building.ID=taken.buildingID
AND '$fromdate' <= taken.to
AND '$todate' >= taken.from
WHERE taken.buildingID IS NULL
ORDER BY building.ID LIMIT ".$l1.",3";

Anon

Yeah, that was what I wanted to do, tnx for helping me out!
i never made query's like that with LEFT JOIN and ON ...

I need to add 2 more tables to that query but offcaurse I don't know how 🙁
i tried it my way but that doesn't work

lets name the tables table1 and table2
and they also have a table1.buildingID = building.ID and table1.building.ID = table2.building.ID

I did it like this:
SELECT DISTINCT building.ID
FROM building, table1, table2
LEFT JOIN taken
ON building.ID=taken.buildingID
AND taken.buildingID=table1.buildingID
AND table1.buildingID=table2.buildingID
AND '$fromdate' <= taken.to
AND '$todate' >= taken.from
WHERE taken.buildingID IS NULL
ORDER BY building.ID LIMIT ".$l1.",3";

can you help me out again plz ?
I'm allmost there now 🙂

lots of greetz and many tnx,
Neutje
http://www.neutje.be

tomhath

Assuming you require a match on buildingID in the first three tables and there are no other conditions, you should use an INNER JOIN (I didn't test this):

SELECT DISTINCT building.ID
FROM building
INNER JOIN table1
ON building.ID=table1.buildingID
INNER JOIN table2
ON building.ID=table2.buildingID

LEFT JOIN taken
ON building.ID=taken.buildingID
AND taken.buildingID=table1.buildingID
AND table1.buildingID=table2.buildingID
AND '$fromdate' <= taken.to
AND '$todate' >= taken.from
WHERE taken.buildingID IS NULL
ORDER BY building.ID LIMIT ".$l1.",3";

tomhath

I messed it up, forgot to remove some of what you had.

SELECT DISTINCT building.ID
FROM building
INNER JOIN table1
ON building.ID=table1.buildingID
INNER JOIN table2
ON building.ID=table2.buildingID
LEFT JOIN taken
ON building.ID=taken.buildingID
AND '$fromdate' <= taken.to
AND '$todate' >= taken.from
WHERE taken.buildingID IS NULL
ORDER BY building.ID LIMIT ".$l1.",3";