+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=22)
+--- Thread: How to select all rows from left table, exclude right tables matching rows in SQL (/showthread.php?tid=60596)
How to select all rows from left table, exclude right tables matching rows in SQL - El Forum - 05-04-2014
[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.
How to select all rows from left table, exclude right tables matching rows in SQL - El Forum - 05-05-2014
[eluser]Tpojka[/eluser]
See if this article can help.
How to select all rows from left table, exclude right tables matching rows in SQL - El Forum - 05-06-2014
[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.
How to select all rows from left table, exclude right tables matching rows in SQL - El Forum - 05-06-2014
[eluser]InsiteFX[/eluser]
In the future please post your questions in the correct forum topic.