MySQL Query to find available premises

squire · #1 (**permalink**) 01-25-05, 22:13

Hi guys,

I have a problem with a MySQL query. I have two tables, shown below:

Table 1: PREMISES
prem_id
address
size
price

Table 2: TENANTS
ten_id
name
type
description
prem_id

I am trying to do a query that will find any premises that are NOT occupied by a tenant.

I was using this code:

Code:

select p1.address, p1.size, p1.price FROM premises AS p1, 
tenants AS p2 WHERE p1.prem_id != p2.prem_id

BUT this displays all the premises that are not the same as the prem_id in the tenants table and does this for each tenant creating duplicated results.

Can anyone help me fix this query?

Thanks in advance.

r937 · #2 (**permalink**) 01-25-05, 23:38

you need a LEFT OUTER JOIN looking for unmatched rows (null in the right table join field)

yes, i realize it looks weird the first time you see it

Code:

select p1.address
     , p1.size
     , p1.price 
  FROM premises AS p1
left outer
  join tenants AS p2 
    on p1.prem_id 
     = p2.prem_id
 where p2.prem_id is null

squire · #3 (**permalink**) 01-26-05, 06:29

Hi r937,

THANK YOU very much. Your code worked perfectly! I have been learning SQL and was thinking it may have something to do with joins but I have no proper knowledge of how to use them. This will help me greatly.

Thank you again.

Internet Services

Web Development

Digital Marketing

Consumer Tech