Search code examples
sqldistinct

Results from two tables with DISTINCT

I have a query which returns all unique values from a table.

punches table:

SELECT DISTINCT user_id 
FROM punches 
WHERE organisation_id  = '$current_organisation_id';

This will list of user_id's.

I also need to get the result of "name" of each user, that is stored in "user" table and also has "user_id" as primary key.

Many thanks

I could get this results with two different SQL queries, but I know it is possible to do in one query.

Solution

  • Sounds like you actually need an IN or EXISTS query, with users as the outer table.

    SELECT
  u.user_id,
  u.name
FROM users u
WHERE u.user_id IN (
    SELECT p.user_id
    FROM punches p
    WHERE p.organisation_id  = '$current_organisation_id'
);

    I sincerely hope $current_organisation_id is not some kind of SQL injection. If so, you should be using a parameter

        WHERE p.organisation_id  = @current_organisation_id