[PHP-DB] how to get records from one table that are not in another table
hi all, can anyone pls help me out to make the query like.. if two tables have some comman records then how to find records from one table that are not in another table. eg... i have one table room type master (room_type_m) fields : room_type_id, room_type_name and other details and another table hotel room type relation (hotel_rtype_r) fields : hotel_id and room_type_id and some other fields this table will contain multiple room_type_ids for one hotel_id i want to print those room_type_id (from room_type_m) that are not present in relation table ie .(hotel_rtype_r) regards, Smita _ It's all happening @ F1. Feel the thrill! http://server1.msn.co.in/sp03/formula2003/index.asp Race along right here! -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] how to get records from one table that are not in another table
I'd suggest you pick up an SQL book or do some more reading if you don't understand that. -- ---John Holmes... John, Do you have a good book recommendation for MySQL.. I'm looking for more knowledge on improving my queries, etc. LEFT JOINS were completely unfamiliar (still are) until you helped me with my 3 query into 1 problem I had last week. Any thoughts? Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals - www.phparch.com -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get records from one table that are not in another table
thanks John, i was knowing that LEFT JOIN would work, but i was not knowing exact syntax where to put IS NULL. anyway, thanks ! still problem is not sloved :-( as u suggested following query worked fine SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL but there is one more criteria, hotel_id from table hotel_room_type_d should be equal to some value i modified it like this... SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL AND B.hotel_id = '$hotel_id' is it necessary to select hotel id also, ie. SELECT A.room_type_id, B.hotel_id FROM room_type_m A LEFT JOIN hotel_room_type_d B. there might be some syntax mistake... can u pls correct it?? it would be great help for me. but one more question ... follwoing query didnt work... SELECT A.room_type_id FROM room_type_m A WHERE A.room_type_id NOT IN(select B.room_type_id from hotel_room_type_d B) im not sure about syntax... Smita From: John W. Holmes [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Smita Manohar [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: [PHP-DB] how to get records from one table that are not in another table Date: Sun, 27 Jul 2003 09:44:04 -0400 Smita Manohar wrote: can anyone pls help me out to make the query like.. if two tables have some comman records then how to find records from one table that are not in another table. eg... i have one table room type master (room_type_m) fields : room_type_id, room_type_name and other details and another table hotel room type relation (hotel_rtype_r) fields : hotel_id and room_type_id and some other fields this table will contain multiple room_type_ids for one hotel_id i want to print those room_type_id (from room_type_m) that are not present in relation table ie .(hotel_rtype_r) You need a LEFT JOIN SELECT r.room_type_id FROM room_type_m r LEFT JOIN hotel_rtype_r h ON r.room_type_id = h.room_type_id WHERE h.room_type_id IS NULL I'd suggest you pick up an SQL book or do some more reading if you don't understand that. -- ---John Holmes... Amazon Wishlist: www.amazon.com/o/registry/3BEXC84AB3A5E/ PHP|Architect: A magazine for PHP Professionals www.phparch.com _ They are beautiful. They are in danger. http://server1.msn.co.in/Slideshow/BeautyoftheBeast/index.asp Our four-legged friends. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] how to get records from one table that are not in another table
heyy... i put second condition (after AND) in the wrong place.. it should be after LEFT JOIN ON not after WHERE... following is the correct query... SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id AND B.hotel_id = '$hotel_id' WHERE B.room_type_id IS NULL previously i was doing SELECT A.room_type_id, room_type_desc FROM room_type_m A LEFT JOIN hotel_room_type_d B ON A.room_type_id = B.room_type_id WHERE B.room_type_id IS NULL AND B.hotel_id = '$hotel_id' with this query it was only printing messege as,query executed successfully in phpmyadmin but not printing the result. anyway, now my problem is solved.. thanks ! - smita _ Cool new emoticons. Lots of colour! http://server1.msn.co.in/sp03/messengerpromo/index.asp On MSN Messenger V6.0 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
