Member Avatar for dsgnews

Hi friends, I have very little knowledge of JOINs. Please, help me out in rewriting my sub-query to some kind of JOIN for earlier MySQL version

TABLES IN MY DATABASE:

Room (room_ no, room_ type, price)
Guest (guest_ no, Fname, Sname, address)
Booking (room_no, guest_no, Fname, Sname, arrival_date,
departure_date, emp_no, user_name, password)
Employee (emp_no, Fname, Sname, user_name, password)
Admin (admin_no, Fname, Lname, user_name, password)

QUERY: Search for the available rooms:

SUBQUERY: This did not work on the remote server

Select *
From Room R
Where R.room_no
NOT
IN(
Select B.room_no
From R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrival_date = ‘2005-03-23’
And B.departure_date = ‘2005-04-30’
)

ATTEMPTED JOIN: not sure at all if it is correct

Select *
From room R, booking B
Where R.room_no = B.room_no
And R.room_type = ‘single’
And B.arrivel_date = ‘2005-04-23’
And B.departure_date=’2005-04-30’ ;

I hope to hear from you soon folks. Thanks

  • 3 Contributors
  • 2 Replies
  • 110 Views
  • 3 Months Discussion Span
  • Latest Post Latest Post by angelo

Dani AI

Generated

A reliable way to get "single" rooms that are free for an entire requested interval (useful when running on older MySQL that lacked subqueries) is to LEFT JOIN Booking to Room using an interval-overlap test in the ON clause, then select rows where the booking side is NULL. 's suggestion to use LEFT JOIN ... IS NULL is the right direction — the missing piece is the correct date-overlap condition and fixing the column-name typos seen in the original post (for example, arrivel_date).

SELECT R.*
FROM Room AS R
LEFT JOIN Booking AS B
  ON R.room_no = B.room_no
  AND B.arrival_date <= '2005-04-30'
  AND B.departure_date >= '2005-03-23'
WHERE R.room_type = 'single'
  AND B.room_no IS NULL;

The logic: the ON clause only joins bookings that overlap the requested interval (two intervals overlap when arrival <= requested_end AND departure >= requested_start). Rooms that have no overlapping booking will have B.room_no IS NULL and therefore are available. Important notes: use consistent DATE or DATETIME types (cast or truncate times if needed), prefer the LEFT JOIN pattern on MySQL versions before 4.1 that do not support subqueries, and avoid NOT IN when the subquery can return NULLs (use NOT EXISTS or LEFT JOIN/IS NULL instead when subqueries are available). Also check column-name typos, supply dates in YYYY-MM-DD form, and add an index on Booking(room_no, arrival_date, departure_date) for performance.

Further reading: MySQL JOIN syntax and behavior (MySQL JOINs) and subquery notes if later upgrading to a MySQL that supports them (MySQL subqueries).

Recommended Answers

All 2 Replies

Member Avatar for DanceInstructor

Please don't double post.

Member Avatar for angelo

----Angelo----- :lol:

My script below is working and i hope same on you....


SELECT table1.*
FROM menu
LEFT JOIN
table2
ON
= table2.id
WHERE table2.id IS NULL

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.