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" <holmes072000@charter.net>
Reply-To: holmes072000@charter.net
To: Smita Manohar <smitamanohar@hotmail.com>
CC: php-db@lists.php.net
Subject: Re: 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
