CODEWITHSUNDEEP
mysqlsql
John Kariuki
John Kariuki

Reputation: 5724

complex SQL statement

I have been coming up with a complex SQL statement for the description below. So I have 3 tables as explained

Clients

  1. client_id
  2. name
  3. email
  4. booked [can be either 0 or 1]

Accom

  1. accom_id
  2. client_id
  3. checkin [timestamp]
  4. checkout [timestamp]

Conf

  1. conf_id
  2. client_id
  3. checkin [timestamp
  4. checkout

So The query is. I want to select all records for clients who are booked in [client.booked = 1] and their respective records in Accom or conf OR both between a checkin and checkout timestamp that is provided by the user.

This is what I have.

SELECT `client_id` FROM `accomm` WHERE `checkin` >= $fro AND `checkout` <= $to
SELECT `client_id` FROM `conf` WHERE `checkin` >= $fro AND `checkout` <= $to

Obviously this selects all the client_id's between the checkin and checkout provided, but do not filter the ID's who's booked value in clients is = 1

Upvotes: 0

Views: 194

Answers (3)

Arth
Arth

Reputation: 13110

I wouldn't say this is too complex:

   SELECT DISTINCT c.* 
     FROM client c
LEFT JOIN accom a
       ON a.client_id = c.client_id
      AND a.checkin BETWEEN $from AND $to
LEFT JOIN conf co
       ON co.client_id = c.client_id
      AND co.checkin BETWEEN $from AND $to
    WHERE c.booked = 1
      AND a.client_id IS NOT NULL OR co.client_id IS NOT NULL

This assumes that you want just the client records and no duplicates.

Upvotes: 2

tallungulate
tallungulate

Reputation: 181

SELECT Client.client_id, name, email, facility_type, facility_id, checkin, checkout
  FROM Client INNER JOIN (
       SELECT 'Accom' AS facility_type, accom_id AS facility_id, client_id, checkin, checkout
         FROM Accom
       UNION
       SELECT 'Conf' AS facility_type, conf_id AS facility_id, client_id, checkin, checkout
         FROM Conf
       ) AS sub
    ON Client.client_id = sub.client_id
 WHERE booked = 1 AND checkin >= $from AND checkout <= $to;

Upvotes: 0

Christian
Christian

Reputation: 7320

   SELECT c.* 
     FROM client c
    WHERE c.booked = 1
      AND ( 
            EXISTS (SELECT 1 
                      FROM accom a
                     WHERE a.client_id = c.client_id
                       AND a.checkin BETWEEN $from AND $to
                   )
            OR 
            EXISTS (SELECT 1
                      FROM conf co
                     WHERE co.client_id = c.client_id
                       AND co.checkin BETWEEN $from AND $to
                   )
          )

For better performance, create these indexes:

  • client (booked)
  • conf (client_id, checkin)
  • accom (client_id, checkin)

Upvotes: 0

Related Questions