Welcome Guest, Not a member yet? Register   Sign In
How to select all rows from left table, exclude right tables matching rows in SQL
#1

[eluser]fnbhns[/eluser]
Hi All,

I am new and i need your kind support in this topics. I have 3 tables CAR, RESERVATION & TROUBLE all table got a matching Column, VC_ID. (that is CAR ID)

Want to select all the records from the table CAR, except those matching VC_ID exist in RESERVATION & TROUBLE in a specific DATE. I have written the following code but not working properly, I need your advices and support.

Code:
SELECT car.c_id, car.vc_name
FROM car
LEFT JOIN reservation
ON  reservation.vc_id = car.vc_id
WHERE (
vehicle_reservation.pickup_date NOT
BETWEEN '2014-04-20 06:00:00'
AND '2014-04-20 16:00:00'
)
AND (
vehicle_reservation.return_date NOT
BETWEEN '2014-04-20 06:00:00'
AND '2014-04-20 16:00:00'
)
LEFT JOIN trouble
ON  car.vc_id = trouble.vc_id
WHERE (
trouble.tr_date NOT
BETWEEN '2014-04-20 06:00:00'
AND '2014-04-20 16:00:00'
)

Looking forward of your reply, Thanking you in advance.
#2

[eluser]Tpojka[/eluser]
See if this article can help.
#3

[eluser]jonez[/eluser]
Two ways you can do that, NOT EXISTS and LEFT JOIN tbl2 ON tbl1.id = tbl2.id WHERE tbl2.id IS NULL.

Not exists is slower as it's a subquery, but if you have indices on the primary key it's still quite quick as it's a Boolean expression.

The LEFT JOIN is faster, you do a possible join then use a where condition to filter out anything that did match, leaving you with everything that didn't.
#4

[eluser]InsiteFX[/eluser]
In the future please post your questions in the correct forum topic.




Theme © iAndrew 2016 - Forum software by © MyBB