1 
select co.id, udf.string_val, udf.id 

from customer_order co left join user_def_fields as udf on co.id = udf.document_id

where status = 'h' and order_date between '1/1/2016' and '12/31/2016' 

and co.id <> (select document_id from user_def_fields 

where (string_val = 'questions' or string_val = 'credit card' or string_val = 'credit hold' 
or string_val = 'design'))

co.id | String_val | udf.id
------|------------|--------
9798  | QUESTIONS  |UDF-000054 
9798  | RUSH ORDER |UDF-000047
9798  | RUSH ORDER |UDF-000024
9799  | RUSH ORDER |UDF-000047
9799  | RUSH ORDER |UDF-000024
9799  | DESIGN     |UDF-000054
9801  | RUSH ORDER |UDF-000047
9801  | RUSH ORDER |UDF-000024
9802  | NULL       |NULL
9803  | RUSH ORDER |UDF-000047
9803  | RUSH ORDER |UDF-000024
9803  | CHECKED    |UDF-000054

Here is a sample of the query results without any filters. Co.id is the order number, the various string_vals are notes within there respective udf fields, udf.id is the field id, and document_id is the order number reference in the udf table. Customer service enters 'Rush Order' in two fields, which is why it shows up twice. Sometimes no notes are entered and the udf fields are null. I really just need the co.id results returned and to be unique. From the above data sample, I want my query to return

co.id 
------
9801
9802
9803

My current query gives me an error because it returns multiple results.

Improve this question
4
  • 1
    I didn't understand your output.Can you please explain why you excluded 9798,9789,9799 Commented Aug 10, 2016 at 16:50
  • Does this work:select co.id, udf.string_val, udf.id from customer_order co left join user_def_fields udf on co.id = udf.document_id where status = 'h' and order_date between '1/1/2016' and '12/31/2016' and co.id not in (select document_id from user_def_fields where (string_val = 'questions' or string_val = 'credit card' or string_val = 'credit hold' or string_val = 'design')) Commented Aug 10, 2016 at 16:53
  • The purpose of the report is to display orders that are on hold ('h'), but don't have 'Questions', 'credit card', 'credit hold', or 'Design' holds on them. '9801', '9802', and '9803' don't have any of these conditions. But I only want to return one row for them. Commented Aug 10, 2016 at 16:58
  • AND co.id <> (SELECT should be AND co.id NOT IN (SELECT if your query returns multiple results Commented Aug 10, 2016 at 18:37

3 Answers 3

Reset to default
1

Essentially, you want to use the "Distinct" keyword. Distinct will suppress rows from the result set that have exactly the same data as another row already in the set. The issue will be that if you want truly distinct co.id values, that's the only column you'll be able to return (much like in your desired results), because distinct will return all rows with a distinct combination of any fields in the select list, which could still give you duplicate co.id values if string_val or udf.id differ between results. Your query would look like:

select distinct co.id    
from customer_order co 
left join user_def_fields udf 
    on co.id = udf.document_id    
where status = 'h' and order_date between '1/1/2016' and '12/31/2016'     
and co.id not in (select document_id from user_def_fields
    where (string_val = 'questions' or string_val = 'credit card' 
        or string_val = 'credit hold' or string_val = 'design'))
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

changing <> to not in or not exists will help when subquery may return more than one value
I get zero results when running the above query.
the status field could be messing with you; is that a field on customer_order or user_def_fields?
Status is in customer_order. When I take it out completely, it still returns zero rows.
K. Since you no longer need the join to user_defined_fields to put UDF data in the select list, you might try removing it and just keeping the subquery.
|
0

You can do this to avoid using DISTINCT.

SELECT  co.id
FROM    customer_order co
WHERE   status = 'h'
        AND order_date BETWEEN '1/1/2016' AND '12/31/2016'
        AND NOT EXISTS ( SELECT 1
                         FROM   user_def_fields udf
                         WHERE  udf.document_id = co.id
                                AND string_val IN ('questions','credit card','credit hold','design')  
        )

or you could use left join and check for null

SELECT  co.id
FROM    customer_order co
        LEFT JOIN user_def_fields udf ON udf.document_id = co.id 
            AND udf.string_val IN ('questions','credit card','credit hold','design') 
WHERE   co.status = 'h'
        AND order_date BETWEEN '1/1/2016' AND '12/31/2016'
        AND udf.document_id IS NULL
Improve this answer

4 Comments

can you explain why the bottom query with the left join works? How does using the IN operator as a join condition filter the results?
it doesn't filter the results.. the filter is AND udf.document_id IS NULL so when you left join to udf on string_val in (list) if it finds a match, you will get values from udf in your result, if there is no match, you will only get values from co in your result.. so by checking a value in udf to see if it's null, you can tell if there was a match or not.
here is an example.. sqlfiddle.com/#!9/6fdf08/2 if you want the records that match the join condition you would add where document_id is not null or just use inner join but if you want the records that did not match the join condition you would add where document_id is null.. this would have to be in the WHERE and not the JOIN
I understand now. That's a great example! Much appreciated.
0

You can use the DISTINCT keyword:

select distinct co.id
from customer_order co left join user_def_fields as udf on co.id = udf.document_id
where status = 'h' and order_date between '1/1/2016' and '12/31/2016' 
and co.id <> (select document_id from user_def_fields 
where (string_val = 'questions' or string_val = 'credit card' or string_val = 'credit hold' or string_val = 'design'))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.