CODEWITHSUNDEEP
phpmysql
Dinesh
Dinesh

Reputation: 19

Avoid duplicating data in php

I have two mysql tables called room_type and resve_room.

The room_type table has room numbers 5, 3, 6 ,9 ,10. The resve_room table has room numbers 3 and 9.

How can I write an SQL query that filters out reserved room number 3 and 9 from the room_type table. I want to return the following room numbers 5,6,10.

I have tried using the following SQL but it only returned 3 and 9 :

SELECT room_type.room_no 
FROM room_type,in_hand_room 
WHERE in_hand_room.room_no=room_type.room_no 
&& room_type.room_id='$room_id

Upvotes: 2

Views: 99

Answers (2)

geoandri
geoandri

Reputation: 2428

Try the follwoing 

 SELECT room_type.room_no 
 FROM room_type 
 WHERE  room_type.room_no NOT IN (SELECT resve_room.room_no from resve_room )

or in_hand_room instead of resve_room beacuse it 's not clear from your desc which one is your reservation table

Upvotes: 1

Umair Ayub
Umair Ayub

Reputation: 21361

Try something like this

   SELECT B.Accountid 
  FROM TableB AS B 
  LEFT 
  JOIN TableA AS A 
    ON A.ID = B.Accountid 
   AND A.ID IS NULL;

Or maybe this

select ids from TableB EXCEPT select id from TableA

Upvotes: 1

Related Questions