CODEWITHSUNDEEP
sqlsql-server
o.o
o.o

Reputation: 3751

Select rows where string does not repeat

I'm not sure how to phrase this, but I have two tables that I use INNER JOIN on to count the number of records I have. This works fine but the problem is that I have some rows in table1 where some of the records have a string that can appear in another record. Something like this:

table1                   table2
------                   ------
id    string             id    table1_id    some_column
01    aaa                01    01           1
02    bbb                02    02           3
03    aaa                03    03           1
04    ccc                04    04           4
05    bbb                05    05           2
...                      ...

My query looks like this:

SELECT COUNT(*) FROM table1
INNER JOIN table2 ON table1.id = table2.table1_id

This query works fine, but I want to be able to get distinct values. So my query should bring me back only these records:

table1                   table2
------                   ------
id    string             id    table1_id    some_column
01    aaa                01    01           1
02    bbb                02    02           3
04    ccc                04    04           4
...                      ...

As you can see, it doesn't show any additional records where they share the same string. What would I write after or before my INNER JOIN? Any help would be appreciated.

Upvotes: 2

Views: 637

Answers (4)

Steve Lovell
Steve Lovell

Reputation: 2564

If you really only want the count you can do this:

SELECT COUNT(DISTINCT table1.string)
FROM table1 INNER JOIN table2 ON table1.id = table2.table1_id

Some of the other answers are clearly better if you want do do something with the distinct values other than just count them.

Upvotes: 0

Das Nuk
Das Nuk

Reputation: 46

SELECT DISTINCT count(*) from table1 inner join table2 on table1.id = table2.table1_id

This will give you the count of distinct values in the tables

Upvotes: 0

SqlZim
SqlZim

Reputation: 38033

using common table expression with row_number() to select the top 1 per group of string.

;with cte as (
  select *
    , rn = row_number() over (partition by string order by id)
  from t1
)

select count(*)
  from cte
    inner join t2
      on cte.id = t2.table_1id
     and cte.rn = 1

using a subquery instead of the cte:

select count(*)
  from (
    select *
      , rn = row_number() over (partition by string order by id)
    from t1
  ) sub
    inner join t2
      on sub.id = t2.table_1id
     and sub.rn = 1

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133370

You can group by the table 1 

select count(*) 
from (
    select min(id) as id, string 
    from table1 
    group by string
) t1
inner join table2 on t1.id = table2.table1_id

Upvotes: 1

Related Questions