CODEWITHSUNDEEP
mysql
stickerbush1970
stickerbush1970

Reputation: 1

Creating a subquery in mysql

I am creating a query that returns a list of real estate agents (first name, last name, work phone, mobile phone, and email address) that has a listing that is under $500,000.

Table: listings

Columns:

listing_key int PK 
Agents_key int 
listing_status int 
listing_type int 
date_listed date 
date_expires date 
date_unlisted date 
reason_unlisted int 
address varchar(50) 
city varchar(30) 
state char(2) 
zip_code varchar(10) 
lot_number varchar(50) 
residential_area int 
listing_price int 
listing_agreement_signed_date date 
remark varchar(1000)

Table: agents

Columns:

agent_key int PK 
office_key int 
first_name varchar(20) 
last_name varchar(25) 
work_phone varchar(10) 
mobile_phone varchar(10) 
email_address varchar(50) 
license_number varchar(30) 
remarks varchar(1000)

This is the code I have written:

SELECT first_name, last_name, work_phone, mobile_phone, email_address
FROM agents
WHERE listing_price <=500000
ORDER BY agent_key

I am running into

error 1054: Unknown column 'listing_price' in where clause.

What am I doing wrong?

I would like something returned like this:

ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE  CUST_CODE       AGENT_CODE      ORD_DESCRIPTION

200114       3500           2000 15-AUG-08 C00002          A008
200122       2500            400 16-SEP-08 C00003          A004
200118        500            100 20-JUL-08 C00023          A006
200119       4000            700 16-SEP-08 C00007          A010
200121       1500            600 23-SEP-08 C00008          A004
200130       2500            400 30-JUL-08 C00025          A011
200134       4200           1800 25-SEP-08 C00004          A005
200108       4000            600 15-FEB-08 C00008          A004
200103       1500            700 15-MAY-08 C00021          A005

Upvotes: 0

Views: 49

Answers (3)

nbk
nbk

Reputation: 49375

If you want a subquery you can use:

SELECT first_name, last_name, work_phone, mobile_phone, email_address
FROM agents
WHERE agent_key IN ( SELECT DISINCT Agents_key  
                     FROM  listings 
                     WHERE listing_price <=500000)
ORDER BY agent_key

But if you need some columns from listigs you must use a JOIN

Upvotes: 1

bradstw
bradstw

Reputation: 103

You are missing the JOIN. Here is the link to the documentation on JOIN CLAUSE in MySQL - https://dev.mysql.com/doc/refman/8.0/en/join.html

SELECT a.first_name, a.last_name, a.work_phone, a.mobile_phone, a.email_address
FROM agents a
JOIN listings l ON a.agent_key = l.Agents_key
WHERE l.listing_price <=500000
ORDER BY a.agent_key

Upvotes: 1

Keneni
Keneni

Reputation: 705

The table agents doesn't have the column listing_price. You should be using the table Listings for the listing price not the agents table.

Upvotes: 0

Related Questions