SELECT-FROM-IF condition in SQL

Question

I have the following SELECT statement:

SELECT 
     "project_id" as "ID"
FROM "projects"

but I want this to happen only when I have only distinct values in this column, such as:

COUNT(DISTINCT "project_id") = COUNT("project_id")

else I would like the program to crash with a message "The IDs do not have unique values".

Any ideas how I should tackle this?

I tried different CASE WHEN scenarios but without any luck.

https://stackoverflow.com/questions/2204250/check-if-checkbox-is-checked-with-jquery — Reporter, Dec 14 '22 at 13:14
I don't understand the question, could you share some sample data and expected output,? — James, Dec 14 '22 at 13:14
Any reason you don't just add a unique constraint or primary key so duplicate values cannot even be inserted? Checking this every time on retrieval is not efficient, whether done in SQL or on the client end. — Jeroen Mostert, Dec 14 '22 at 13:26
@JeroenMostert The unique constraint would work great, but right now I have to work with the given data, without being able to modify it. I have some tables where the "project_id" column values are not unique and I somehow have to flag these tables within a select statement because the final task is to create views. — singlequit, Dec 14 '22 at 13:35

score 0 · Answer 1 · answered Dec 14 '22 at 14:37

This is more complicated than you think because you are trying to return 2 different datasets from the same query - most RDBMS systems do not like this. The datatype of each column must always be the same, and the number of columns needs to be the same. What you are trying to do is really better suited to the application layer.

That being said, here is a version that is close to what you ask. I have written it using SQL Server but the actual query itself uses standard SQL. First I will make a table variable with some sample data.

DECLARE @Data TABLE (project_id INT);
-- fill the table with all unqiue values
INSERT INTO @Data(project_id) VALUES (1), (2), (3);

SELECT 
CASE WHEN COUNT(DISTINCT project_id) = COUNT(project_id) THEN CAST(project_id AS VARCHAR)
  ELSE CONCAT(project_id, ' is not unique') END AS [ID]
  FROM @Data 
GROUP BY project_id;

The output of this looks like: |ID| |--| |1| |2| |3|

Now let's look at a version where there are duplicate values:

DECLARE @Data TABLE (project_id INT);
-- fill the table with all unqiue values
INSERT INTO @Data(project_id) VALUES (1), (2), (3);

-- add duplicate values
INSERT INTO @Data(project_id) VALUES (1), (3);
SELECT 
CASE WHEN COUNT(DISTINCT project_id) = COUNT(project_id) THEN CAST(project_id AS VARCHAR)
  ELSE CONCAT(project_id, ' is not unique') END AS [ID]
  FROM @Data 
GROUP BY project_id;

And the output looks like:

ID
1 is not unique
2
3 is not unique

Again, this isn't exactly what you asked for, but I am not 100% sure that what you are asking can be accomplished in SQL alone.

SELECT-FROM-IF condition in SQL

1 Answers1