Creating a subquery in mysql

Question

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

Please provide sample data and desired results to clarify your question. — GMB
– GMB, Commented Oct 2, 2020 at 21:33

bradstw · Accepted Answer · 2020-10-02 21:48:25Z

1

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

edited Oct 2, 2020 at 21:48

answered Oct 2, 2020 at 21:34

bradstw

1039 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Keneni Over a year ago

Yes that too... since he mentioned a subquery in the title - I decided not go into the join statement alternative.

bradstw Over a year ago

@Keneni, you are right, I did not address the subquery. It is not needed unless I am missing something.

bradstw Over a year ago

@stickerbush1970 updated my answer to use the correct column name from the listings table. I am also assuming Agents_key in the listings table is the same value of the agent_key column in the agents table.

stickerbush1970 Over a year ago

OK, I see what you mean know. Thanks, learning SQL and just not getting the context yet

Keneni Over a year ago

@stickerbush1970 - if you are only trying to use a subquery (which isn't necessary to solve your problem), then make sure that your code refers to the right table - as I explained in my answer post. You are referring to the incorrect table for the listing price column. That is exactly what your error code is telling you.

|

nbk · Accepted Answer · 2020-10-02 21:51:02Z

1

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

answered Oct 2, 2020 at 21:51

nbk

49.6k8 gold badges32 silver badges53 bronze badges

1 Comment

Keneni Over a year ago

Nicely explained @nbk

Keneni · Accepted Answer · 2020-10-02 21:34:52Z

0

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.

answered Oct 2, 2020 at 21:34

Keneni

7055 silver badges12 bronze badges

Collectives™ on Stack Overflow

Creating a subquery in mysql

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related