CODEWITHSUNDEEP
mysqlsqlselect
user2511030
user2511030

Reputation: 515

Single query instead of multiple select statements

Hello I am stuck in a SQL statement situation. Can anyone help me with the statement:

Table 1. 

Customers
customer_id | customer_name | custom_url | customer_desc

Table 2.

Categories
category_id | customer_id | category_name

select customer_id where category_name="Realtor";

gives me a list of customer ids. Now I use them to find all their names, desc and url from the customers table.

I need to do this in a single query and process the customer details to display on the UX. I am really stuck how to do this.

Here is what i have (which I know is wrong)

select * from customers where customer_id = Loop (select customer_id from categories where category_name="Realtor");

Upvotes: 0

Views: 129

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You can do what you want using IN or ANY or EXISTS:

select *
from customers
where customer_id = ANY (select customer_id
                         from categories
                         where category_name = 'Realtor'
                        );

Or:

select *
from customers
where customer_id IN (select customer_id
                      from categories
                      where category_name = 'Realtor'
                     );

Upvotes: 0

Rahul
Rahul

Reputation: 77866

Modify your query to a JOIN query like

select c.* 
from customers c
join categories ca on c.customer_id = ca.customer_id
where ca.category_name='Realtor';

Upvotes: 2

Related Questions