CODEWITHSUNDEEP
mysqlreference
user0000001
user0000001

Reputation: 2233

Select rows that are referenced in another table

I have two tables and they are as follows:

USERS
ORDERS

I want select all users who have at least 1 order or more in the ORDERS table. I know there is an inline query for this in MySQL, but right now I have to select all users and then make another query seeing if each user has an order - all this using a PHP loop.

What I am doing now is not ethically correct, so I basically just want to select all users who have been referenced in the ORDERS table in ONE MySQL query.

Upvotes: 1

Views: 2408

Answers (2)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230336

This is a query you should be using 

select distinct u.* from users u
inner join orders o on o.user_id = u.id;

Note the distinct and u.*. This query will not select fields from orders and it will not select the same user twice (if one has more than one order).

Demo: http://sqlfiddle.com/#!2/6ebcc/3

Upvotes: 6

Iswanto San
Iswanto San

Reputation: 18569

You can use mysql join syntax. Assuming both of your tables has userid column, this is the example :

SELECT * FROM USERS a JOIN ORDERS b ON
a.UserId = b.UserId

This is a simple database operation, see here for the explanation join

Upvotes: -1

Related Questions