How to find unique records present in combined table (composite key)

Answer 1

You can use SELECT COUNT(1), .... GROUP BY

Answer 2

In SQL, you can use the GROUP BY and HAVING clauses to find unique records in a combined table that has a composite key. Here is an example of how you could do this:

SELECT *
FROM combined_table
GROUP BY column1, column2
HAVING COUNT(*) = 1;

This query will return all the records from combined_table that have unique values for the composite key (column1, column2).

Here is how this query works:

The GROUP BY clause groups the records in combined_table by the values in column1 and column2.
The HAVING clause filters the groups to only include groups that have a count of 1, which means they have only one record in the group.
The SELECT * statement at the beginning of the query returns all the columns of the records that are in the groups that match the HAVING clause.

... continue to read the next message

Answer 3

continue......

Note that the columns included in the GROUP BY clause must be part of the composite key, and they must be listed in the same order as the composite key. Also, if the composite key includes more than two columns, you would need to list all of them in the GROUP BY clause.

For example, if the composite key is made up of three columns (column1, column2, column3), the query would look like this:

SELECT *
FROM combined_table
GROUP BY column1, column2, column3
HAVING COUNT(*) = 1;

Answer 4

The combination of fields that make up the composite key must by definition be unique, so I'm a bit confused by what you're asking. If you try to make a composite key and not every combination is unique, it will fail. If you just want to get each unique combination without making keys, you can just use a SELECT DISTINCT statement.

Answer 5

Say the columns you want to count unique combinations are col1, col2, col3 and you have a primary key called id:

SELECT col1,col2,col3,COUNT(id)
FROM table
GROUP BY col1,col2,col3

This will return a table listing how many times each combination of col1,col2,col3 occurs.

Answer 6

Caroline ...yes unique distinct work

Answer 7

But how to use count function in that...??

Answer 8

That works... thank you 😊