CODEWITHSUNDEEP
phpmysqlsqlmysqli
steeped
steeped

Reputation: 2633

Properly joining 2 tables

I have a list of offices in one table, and a list of all the short-term leases each office has.

I am trying to query both tables to display a list of all the offices, and the earliest lease that is booked and the last lease for when it becomes available again.

SELECT offices.* ,MIN(lease.date_start), MAX(lease.date_end) FROM offices, lease WHERE lease.office_id = office.id ORDER BY office.id DESC

Office Table:

id        | office_name | office_description 
1         | North York  | Lorem Ipsum
2         | Toronto     | Lorem Ipsum
3         | Richmond    | Lorem Ipsum

Lease Table:

id        | office_id   | start_date   | end_date 
1         | 1           | 5            | 8
2         | 1           | 3            | 7 
3         | 2           | 1            | 4

The result I am trying to get:

office_id=>1, start_date=>3, end_date=>8
office_id=>2, start_date=>1, end_date=>4
office_id=>3, start_date=>NULL, end_date=>NULL

How would I structure my query to get that result?

Upvotes: 1

Views: 56

Answers (4)

Ashalynd
Ashalynd

Reputation: 12563

SELECT office_id,
MIN(start_date) as start_date,
MAX(end_date) as end_date
FROM office 
LEFT JOIN lease ON (lease.office_id = office.office_id)
GROUP BY office_id
ORDER BY office_id ASC

OR

SELECT office_id, start_date, end_date FROM (
SELECT office_id,
MIN(start_date) as start_date,
MAX(end_date) as end_date
FROM lease GROUP BY office_id
UNION
SELECT id AS office_id,
NULL start_date, NULL end_date
FROM office 
WHERE NOT EXISTS 
(SELECT 1 FROM lease
WHERE office_id=office.id) ) u
ORDER BY office_id ASC

I am not sure which one will be faster.

Upvotes: 0

user2849406
user2849406

Reputation: 195

The following select should give you the desired result:

SELECT o.*, min(l.date_start), max(l.date_end)  
FROM offices o
LEFT JOIN lease l on (
   l.office_id = o.id
)
GROUP BY o.id

Upvotes: 0

Teja
Teja

Reputation: 13534

SELECT CONCAT("office_id=>",O.id,", start_dt=>",B.start_dt,", end_dt=>",B.end_dt)
FROM
office O,
(
 SELECT L.office_id,MIN(start_dt) AS start_dt,MAX(end_dt) AS end_dt
 FROM Lease L
 GROUP BY L.office_id
) B
WHERE O.id = B.office_id;

Upvotes: 0

Cobra_Fast
Cobra_Fast

Reputation: 16071

In order to properly use MIN() and MAX() in this case you need to GROUP BY:

SELECT
    office.*
    MIN(lease.start_date),
    MAX(lease.end_date)
FROM office AS office
LEFT JOIN lease AS lease ON (lease.office_id = office.office_id)
GROUP BY office.office_id
ORDER BY office.office_id DESC

Upvotes: 2

Related Questions