Help with SQl and UNION
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?
SELECT 0 as Used, asset_no, description, date_expires FROM standards WHERE removed=False AND asset_no IN ('HG-4OP','OC-GM','Set 1','Set 9','Set 6')
UNION
SELECT 1 as used, standards.asset_no, standards.description, date_expires FROM standards_used INNER JOIN standards ON standards.asset_no =standards_used.asset_no
ORDER BY asset_no
SELECT 0 as Used, asset_no, description, date_expires FROM standards WHERE removed=False AND asset_no IN ('HG-4OP','OC-GM','Set 1','Set 9','Set 6')
UNION
SELECT 1 as used, standards.asset_no, standards.description, date_expires FROM standards_used INNER JOIN standards ON standards.asset_no =standards_used.asset_no
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.
ASKER
This is MS Access SQL
ASKER
correct about UNION ALL. but that's a side issue.
Doed NOT EXIST work for MS access SQL?
Doed NOT EXIST work for MS access SQL?
You're going to have to tell us in words what you are trying to do. What is the purpose of the 0 and 1 values for Used? That is what is preventing the union from summarizing away duplicates.
ASKER
Sorry. let me clarify. Also, I copied the wrong SQL in the original post. And now I've add images to this post that shows the table layouts.
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
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
This worked for me. Thanks!!
1) In your first SQL query, LEFT JOIN to the standards_used table and in your WHERE clause add a condition to include records where standards_used.asset_no
is NULL (standards_used.asset_no IS NULL)
1) In your first SQL query, LEFT JOIN to the standards_used table and in your WHERE clause add a condition to include records where standards_used.asset_no
is NULL (standards_used.asset_no IS NULL)
Are you using SQL Server ? Also since you are hardcoding 1 and 0 as Used column in both the select queries UNION is useless , in that case use UNION ALL.