SQL WHERE statement returning both or conditions

I’m trying to return a single row based on this where statement

WHERE
    (C.id = G.community_id
     AND P.service_type_id = G.service_type_id 
     AND I.unit_class_id = G.unit_class_id)
    OR
    (C.id = G.community_id
     AND P.service_type_id = G.service_type_id)

The issue is I have to get a row based on multiple criteria and the more that match determines the final match. The statement above returns a row just fine if it matches the bottom or statement, but if it matches the first OR it returns results for both statements.

LIMIT 1 doesn’t work either as sometimes it gives preference to the wrong result.

EDIT:

community_id	service_type_id	unit_class_id
1	1	1
1	1	null

Because of the way the table is both rows are true, my understanding was SQL took the first one that was true and returned it.

I apologize for not a lot of info I was hoping maybe there was just a bit of info I was missing. Here is my query.

CREATE VIEW view_invoice_line_item_gl_code 
AS
    SELECT 
        DISTINCT(ILI.invoice_line_item_id) AS invoice_line_item_id, 
        C.community_id AS community_id,
        S.service_type_id AS service_type_id,
        U.unit_class_id AS unit_class_id,
        LIP.line_item_provider_id AS line_item_provider_id,
        (SELECT gl_code_id
         FROM gl_code G  
         WHERE (C.community_id = G.community_id
                AND P.service_type_id = G.service_type_id)
           AND ((G.unit_class_id IS NULL 
                 AND G.line_item_provider_id IS NULL)
                OR
                (I.unit_class_id = G.unit_class_id
                 AND G.line_item_provider_id IS NULL)           
                OR
                (I.unit_class_id = G.unit_class_id
                 AND ILI.line_item_provider_id = G.line_item_provider_id)
               )) AS gl_code_id
    FROM 
        invoice I
    JOIN 
        invoice_line_item ILI ON (ILI.invoice_id = I.invoice_id)
    JOIN 
        invoice_header IH ON (I.invoice_header_id = IH.invoice_header_id)
    JOIN 
        provider_community_account PC  ON (I.provider_community_account_id = PC.provider_community_account_id)
    JOIN 
        line_item_provider LIP ON (ILI.line_item_provider_id = LIP.line_item_provider_id)
    JOIN 
        unit_class U ON (I.unit_class_id = U.unit_class_id)
    JOIN 
        community C ON (PC.community_id = C.community_id) 
    JOIN 
        provider P ON (PC.provider_id = P.provider_id)
    JOIN 
        service_type S ON (P.service_type_id = S.service_type_id)

Answer

I’m assuming that you want to get the record that matches the most conditions first. One way to do that is to order by the number of matching conditions (in this case only one condition is different):

SELECT TOP 1 ...
FROM ...
WHERE
    C.id = G.community_id
    AND P.service_type_id = G.service_type_id 
ORDER BY CASE WHEN I.unit_class_id = G.unit_class_id THEN 1 ELSE 0 END DESC

Advertisement

Answer