Select Distinct Values from One Table and Join With Another Table

DISTINCT from One table and INNER JOIN with another table in snowflake

If the goal is to use JOIN on T1 table only as a filter, IN/EXISTS could be used:

SELECT T2.*
FROM DB1."PUBLIC"."TABLE2" AS T2 
WHERE T2."Id" IN (SELECT T1."Id" FROM DB1."PUBLIC"."TABLE1" AS T1)
  AND T2."queryGroupName" NOT IN ('DELETE');

How to get value from one table based on distinct values from another table?

Please try following:

select result.subject_id from student
join result on student.id =result.student_id where
student.primary_skill in
(select primary_skill from student group by primary_skill having COUNT(*)=1)

Distinct IDs from one table for inner join SQL

Your first and second query are similar.(just that you can not use ; inside your query) Both will produce the same result.

Even your second query which you think is giving you desired output, can not produce the output what you actually want.

Distinct works on the entire column list of the select clause.

In your case, if for the same a.id there is different a.test_group available then it will have multiple records with same a.id and different a.test_group.

Selecting distinct values from a join of two large tables

With knowledge about your data you can try something like this:

SELECT
    b.*
FROM
    (
        SELECT
            DISTINCT `breed`
        FROM
            `animal_breeds`
    ) AS b
WHERE
    EXISTS (
        SELECT
            *
        FROM
            animal_breeds AS ab
            INNER JOIN animals AS a ON ab.animal_id = a.id
        WHERE
            b.breed = ab.breed
            AND a.owner_id = ?
    )
;

The idea is to get short list of distinct breeds without any filtering (for small list it would be quite fast) and then filter further the list with correlated subquery. As the list is short it would be only few subqueries executed and they will only check for existence that is much faster that any grouping (distinct == grouping).

This will only work if your distinct list is quite short.

With random generated data based on your answers the above query gave me the following execution plan:

id  select_type table   partitions  type    possible_keys   key key_len ref rows    filtered    Extra
1   PRIMARY <derived2>      ALL                 2   100.00  
3   SUBQUERY    a       ref PRIMARY,animals_animal_id_index animals_animal_id_index 153 const   1011    100.00  Using index
3   SUBQUERY    ab      ref animal_breeds_animal_id_breed_unique,`animal_breeds_animal_id_index`,animal_breeds_animal_id_index  `animal_breeds_animal_id_index` 5   test.a.id   2   100.00  Using index
2   DERIVED animal_breeds       range   animal_breeds_animal_id_breed_unique,`animal_breeds_breed_index`,animal_breeds_breed_index  `animal_breeds_breed_index` 1022        2   100.00  Using index for group-by

Alternatively, you can try to create WHERE clause like this:

...
WHERE
    b.breed IN (
        SELECT
            ab.breed
        FROM
            animal_breeds AS ab
            INNER JOIN animals AS a ON ab.animal_id = a.id
        WHERE
            a.owner_id = ?
    )

Join mysql table with distinct value from another table

The columns that you want in the results are councel (actually only one of all its values) from cp_counsel and counsel_id from cp_cases_counsel, so you must group by them and select them:

SELECT a.counsel, t.counsel_id, COUNT(*) AS total 
FROM cp_cases_counsel t
INNER JOIN (
  SELECT enrolment_number, MIN(counsel) AS counsel
  FROM cp_counsel
  GROUP BY enrolment_number
) a ON a.enrolment_number = t.counsel_id
GROUP BY a.counsel, t.counsel_id;

Join Only Unique Values From 2 Tables

Are you trying something like this:

 select t1.column2,t2.column2 
 from table1 t1 
 inner join 
 table2 t2 on t1.column2= t2.column2 
 where t1.column1 in ('c1value2', 'c1value3')
 group by t1.column2,t2.column2 ;

create table table1  (
  column1 varchar(50), 
  column2 varchar(50) );

insert into table1 values ( 'c1value1', 'c2value1-1'),
                          ( 'c1value2', 'c2value1-2'),
                          ( 'c1value3', 'c2value1-3');

create table table2  (
  column1 varchar(50), 
  column2 varchar(50) );

insert into table2 values ( 'c1value1', 'c2value1-1'),
                          ( 'c1value1', 'c2value2-1'),
                          ( 'c1value1', 'c2value3-1'),
                          ( 'c1value2', 'c2value1-2'),
                          ( 'c1value3', 'c2value1-3');

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/70

Edit: If you want the unique values of column2 of both tables based on column1 of table1, you can use a subquery for selecting the distinct values for column1 and use it in the where condition.

 select t1.column2,t2.column2 
 from table1 t1 
 inner join 
 table2 t2 on t1.column2= t2.column2 
 where t1.column1 in (select distinct column1 from table1)
 group by t1.column2,t2.column2 ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/72

SQL - Select distinct rows and join them with another table to get data

SELECT p.person_id AS person_id, p.name AS name FROM person p, cars c    
WHERE p.person_id = c.person_id    
GROUP BY b.brand_id

How to join two tables on distinct values of a column?

You can easily accomplish this with row_number window function. See query below:

select t1.id, t1.name, t1.pets, t2.address, t2.job
from (
    select *,
           row_number() over (partition by [name] order by id) rn 
    from Table1
) t1 
join table2 t2 on t1.name = t2.name
where t1.rn = 1

Distinct Count with data from another table

You must join users with LEFT joins to tags and pictures and aggregate:

SELECT u.id, u.name, COUNT(DISTINCT p.album_id) counter
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
LEFT JOIN pictures p ON p.id = t.picture_id
GROUP BY u.id, u.name

If you want the result for a specific user only:

SELECT u.id, u.name, COUNT(DISTINCT p.album_id) counter
FROM users u
LEFT JOIN tags t ON t.user_id = u.id
LEFT JOIN pictures p ON p.id = t.picture_id
WHERE u.id = ?
GROUP BY u.id, u.name -- you may omit this line, because SQLite allows it

Or with a correlated subquery:

SELECT u.id, u.name, 
       (
         SELECT COUNT(DISTINCT p.album_id)
         FROM tags t INNER JOIN pictures p 
         ON p.id = t.picture_id
         WHERE t.user_id = u.id 
       ) counter
FROM users u
WHERE u.id = ?

Replace ? with the id of the user that you want.

---

Related Topics