Help with SQl and UNION
I had this question after viewing Help with SQl and UNION.
I closed this question above after I thought I had a solution, but the solution did not work after all.
I have the SQL below. If there is a situation where both SELECTS return the same record, I get one record where Used = 1 and another where Used = 0. In this case I only want the record where Used = 1. How can I do this? The used =1 column indicates that the standard is used for a Work order, while used = 0 mean that the standard is available to be used, but is not.
SELECT 0 AS Used, standards.asset_no, standards.description, standards.date_expires
FROM standards
WHERE removed=False AND asset_no IN ('HG-4OP','Set 2')
UNION
SELECT 1 AS Used, standards.asset_no, standards.description, standards.date_expires FROM standards_used
INNER JOIN standards ON standards.asset_no =standards_used.asset_no
WHERE standards_used.work_order_
ORDER BY asset_no
I closed this question above after I thought I had a solution, but the solution did not work after all.
I have the SQL below. If there is a situation where both SELECTS return the same record, I get one record where Used = 1 and another where Used = 0. In this case I only want the record where Used = 1. How can I do this? The used =1 column indicates that the standard is used for a Work order, while used = 0 mean that the standard is available to be used, but is not.
SELECT 0 AS Used, standards.asset_no, standards.description, standards.date_expires
FROM standards
WHERE removed=False AND asset_no IN ('HG-4OP','Set 2')
UNION
SELECT 1 AS Used, standards.asset_no, standards.description, standards.date_expires FROM standards_used
INNER JOIN standards ON standards.asset_no =standards_used.asset_no
WHERE standards_used.work_order_
ORDER BY asset_no
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
PatHartman
I mentioned this problem in your other thread. The union will NOT summarize the data if any column is unique. What is the point of the Used field? If you eliminate it, the query will work. If you need to know where the data came from then you need to fix each of the queries to join to the other table and fix them to only return rows where there is no match in the "other" table. This assumes that one table or the other but NOT both will have the record you want.
ASKER
Thanks. I got it to work./