How to count rows matching multiple filters in SQL?-CodePudding

I have data in which I'm aiming to find rows that have unique values of their main_ID column and then count the total of those IDs that also have either of 2 values for another ID column.

I am trying this:

SELECT COUNT(DISTINCT(main_id)) 
FROM (SELECT other_id, main_id FROM database.table WHERE other_id ='5') a INNER JOIN
(SELECT other_id, main_id FROM database.table WHERE other_id ='6') b USING (main_id)

This returns an error at (SELECT saying subquery in FROM must have an alias. I've never coded in SQL before so I'm not sure what to start with addressing this. As I understand it, it wants aliases for the 2 columns - how do I assign these for my inner join?

CodePudding user response：

You need to follow this structure for an inner join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

in your query you need to add the relation between the 2 tables, in this case you need to use the primary key of both tables to make the relation between the tables.

like this example you need to add "On" stament and "table a primary key" equals "table b primary key"

    SELECT COUNT(DISTINCT(main_id)) 
    FROM (SELECT other_id, main_id FROM database.table WHERE other_id ='5') a INNER JOIN
    (SELECT other_id, main_id FROM database.table WHERE other_id ='6') b on 
    a.primary_key=b.priary_key

You can red more information about inner join.

CodePudding user response：

your query can be optimized to this :

select count(*) from (
  select main_id
  from database.table
  where other_id in ('5','6')
  group by main_id
  having count(distinct other_id) = 2
) t