Search code examples
sqloracleoracle11g

categorize rows by count of them which is used in another table

I have 2 tables- customers and documents. Id column of customers table is same with the cs_no column in the documents table and cs_no can be repeated of course. Also in customers table, I have a column named as is_resident (values can be Y or N). I need to query if the customer with is_resident value N has 1 document(If customer id is written only once in cs_no column) - 'one document', if has 2 or more documents - 'some documents', if doesn't have 'no document'. I can't use join

Solution

  • Use a correlated sub-query:

    SELECT id,
       (
         SELECT CASE COUNT(*)
                WHEN 0 THEN 'No Documents'
                WHEN 1 THEN 'One Document'
                ELSE        'Some documents'
                END
         FROM   document d
         WHERE  c.id = d.cs_no
       ) AS documents
FROM   customers c
WHERE  is_resident = 'N'