Getting value from one table with inputs of two different tables

Question

I have these two columns:

1. seatwhich is already filled. seat_id - auto_increment.

seat_id seat type
1       1     1
2       2     1
3       3     1
4       7     2
..     ..     ..

2. date-seat

id  date_booked  seat_id  user_id
1    2016-5-12     4       2
2    2016-5-14     5       3
..    ..           ..      ..

I want to choose a place from this type and given date. for example, if the type is 2 and the date is 2016-5-12. I want to select all seats of type 2 except 7. because seat_id 4, that is, seat 7 already exists on the date 2016-5-12

What I tried:

$type=$_POST['type'];
$flightdate=$_POST['flightdate'];
$sql ="SELECT seat, seat_id FROM seat INNER JOIN `date-seat` ON seat.seat_id=`date-seat`.seat_id WHERE `date-seat`.date_booked<>$flightdate AND seat.type=$type";

this sql gives me places that don't match dates. that is, if I choose type 2 and date 2016-5-12. it only gives seat_id 5. However, I want all seats except seat_id 4. I hope you understand.

Answer 1

Solution No. 1 (using NOT EXISTS):

SELECT 
*
FROM seat S 
WHERE NOT EXISTS(
   SELECT 1
   FROM `date-seat` DS
   WHERE DS.seat_id = S.seat_id
   AND DS.date_booked ='2016-05-12'
)
AND S.type = 2;

№2 ( LEFT JOIN IS NULL):

SELECT 
S.*
FROM seat S 
LEFT JOIN `date-seat` DS 
ON S.seat_id = DS.seat_id AND DS.date_booked = '2016-05-12'
WHERE S.type = 2 AND DS.seat_id IS NULL;

№3 ( NOT IN):

SELECT 
*
FROM seat S
WHERE S.seat_id NOT IN (
      SELECT DS.seat_id
      FROM `date-seat` DS 
      WHERE DS.date_booked = '2016-05-12'
    )
AND S.type = 2;

---

_sql

-- ----------------------------
-- Table structure for `date-seat`
-- ----------------------------
DROP TABLE IF EXISTS `date-seat`;
CREATE TABLE `date-seat` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date_booked` date NOT NULL,
  `seat_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of date-seat
-- ----------------------------
INSERT INTO `date-seat` VALUES ('1', '2016-05-12', '4', '2');
INSERT INTO `date-seat` VALUES ('2', '2016-05-14', '5', '3');
INSERT INTO `date-seat` VALUES ('3', '2016-05-14', '6', '5');

-- ----------------------------
-- Table structure for `seat`
-- ----------------------------
DROP TABLE IF EXISTS `seat`;
CREATE TABLE `seat` (
  `seat_id` int(11) NOT NULL AUTO_INCREMENT,
  `seat` int(11) NOT NULL,
  `type` int(11) NOT NULL,
  PRIMARY KEY (`seat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of seat
-- ----------------------------
INSERT INTO `seat` VALUES ('1', '1', '1');
INSERT INTO `seat` VALUES ('2', '2', '1');
INSERT INTO `seat` VALUES ('3', '3', '1');
INSERT INTO `seat` VALUES ('4', '7', '2');
INSERT INTO `seat` VALUES ('5', '8', '2');

Answer 2

. not exists:

select *
from seat s
where not exists (
    select 1
    from dateseat ds 
    where ds.date_booked = '2016-5-12' and ds.seat_id = s.seat_id
)

Answer 3

try this query:

SELECT * FROM date-seat ds 
RIGHT OUTER JOIN 
seat s ON ds.seat_id=s.seat_id
WHERE 
    ds.seat_id IS NULL 

apply the rest of the filter.

I hope your rdbms supports an external connection.