January 31, 2017 at 6:13 am
I have a query with many fields returning. One field has the possible values, 0, 1 or 2. I want to select rows where that value is 2 only, and if there is no value of 2, I want one row returning data for all other fields but blank for that particular one. Can you help woth the syntax? Ive tried variations of if not exists but am getting errors about more than one row returned.
Example dataset:
Customer.CustomerId,
Customer.OrderNumber,
Staff.StaffGroup,
FROM
Customer, Staff
WHERE
Staff.StaffGroup = '2'
AND Customer.CustomerId = '12345'
I get no rows using the staffgroup = 2 as it doesnt exist. There are four rows with a staff group of 1, but I dont want these retuirned, I only want one row where that is blank?
Thanks
January 31, 2017 at 6:38 am
Instead of declaring a list of tables, use JOINS. Your SQL is malformed, and doesn't include the whole picture, so here's a starting blockSELECT C.CustomerId,
C.OrderNumber,
S.StaffGroup
FROM Staff S
LEFT JOIN Customer C ON --??? What your JOIN criteria is
WHERE S.StaffGroup = '2'
--AND Customer.CustomerId = '12345' -- You can't have this in your WHERE clause,
-- as it's a filter. You could put it in your JOIN
-- but I don't know what your goal is here
-- and why you have a filter on CustomerID
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 6:53 am
Thom A - Tuesday, January 31, 2017 6:38 AMInstead of declaring a list of tables, use JOINS. Your SQL is malformed, and doesn't include the whole picture, so here's a starting blockSELECT C.CustomerId,
C.OrderNumber,
S.StaffGroup
FROM Staff S
LEFT JOIN Customer C ON --??? What your JOIN criteria is
WHERE S.StaffGroup = '2'
--AND Customer.CustomerId = '12345' -- You can't have this in your WHERE clause,
-- as it's a filter. You could put it in your JOIN
-- but I don't know what your goal is here
-- and why you have a filter on CustomerID
Thanks, I was trying to keep the example simple. The code (with a lot removed) looks like this:
SELECT DISTINCT
DimCust.CustId,
DimCust.CustId2,
DimCust.FullName,
DimCust.FullAddress,
DimStaff.ResourceType
FROM DimStaff
LEFT OUTER JOIN DimCustStaff
ON DiStaff.ctrResourceSer = DimCustStaff.ctrResourceSer
RIGHT OUTER JOIN DimCust
ON DimCustStaff.ctrCustSer = DimCust.ctrCustSer
LEFT OUTER JOIN DimLookup
AS DimLookupStaff
ON DimStaff.DimLookupID_ResourceType = DimLookupStaff.DimLookupID
WHERE
(DimCust.CustId = '123456')
AND (ISNULL(DimPCustStaff.PrimaryFlag,'1') = '1' AND ISNULL(DimCustStaff.StaffFlag,'1') = '1'
OR DimCustStaff.PrimaryFlag = 0 OR DimCustStaff.StaffFlag = 0)
ISNULL(DimLookupOnc.LookupDescriptionENU,'X') LIKE 'Level1' AND
DimStaff.ResourceType = '2'
ORDER BY DimCust.CustId
Basically the above will return no rows if the customer does not have an associated DimStaff.ResourceType 2. I want it to return one distinct row if there is a 2, and a blank row if there is any number of rows with anything other than a 2 including if there is nothing at all (only other option here is null or 1)
January 31, 2017 at 7:00 am
macdca - Tuesday, January 31, 2017 6:53 AMThom A - Tuesday, January 31, 2017 6:38 AMInstead of declaring a list of tables, use JOINS. Your SQL is malformed, and doesn't include the whole picture, so here's a starting blockSELECT C.CustomerId,
C.OrderNumber,
S.StaffGroup
FROM Staff S
LEFT JOIN Customer C ON --??? What your JOIN criteria is
WHERE S.StaffGroup = '2'
--AND Customer.CustomerId = '12345' -- You can't have this in your WHERE clause,
-- as it's a filter. You could put it in your JOIN
-- but I don't know what your goal is here
-- and why you have a filter on CustomerIDThanks, I was trying to keep the example simple. The code (with a lot removed) looks like this:
SELECT DISTINCT
DimCust.CustId,
DimCust.CustId2,
DimCust.FullName,
DimCust.FullAddress,
DimStaff.ResourceTypeFROM DimStaff
LEFT OUTER JOIN DimCustStaff
ON DiStaff.ctrResourceSer = DimCustStaff.ctrResourceSerRIGHT OUTER JOIN DimCust
ON DimCustStaff.ctrCustSer = DimCust.ctrCustSerLEFT OUTER JOIN DimLookup
AS DimLookupStaff
ON DimStaff.DimLookupID_ResourceType = DimLookupStaff.DimLookupIDWHERE
(DimCust.CustId = '123456')
AND (ISNULL(DimPCustStaff.PrimaryFlag,'1') = '1' AND ISNULL(DimCustStaff.StaffFlag,'1') = '1'
OR DimCustStaff.PrimaryFlag = 0 OR DimCustStaff.StaffFlag = 0)ISNULL(DimLookupOnc.LookupDescriptionENU,'X') LIKE 'Level1' AND
DimStaff.ResourceType = '2'ORDER BY DimCust.CustId
Basically the above will return no rows if the customer does not have an associated DimStaff.ResourceType 2. I want it to return one distinct row if there is a 2, and a blank row if there is any number of rows with anything other than a 2 including if there is nothing at all (only other option here is null or 1)
Example output and what I need (apologies for the formatting)
CustID Staff Resource Staff Name CustID Staff Resource Staff Name
123 1 Joe Bloggs 123 1 Joe Bloggs
123 1 Jane Doe 123 1 Jane Doe
123 1 Jane Doe 123 2 Miss Piggy
Result Required Result Required
CustID Staff Resource Staff Name CustID Staff Resource Staff Name
123 (null) (null) 123 2 Miss Piggy
January 31, 2017 at 7:22 am
Stab in the dark, what happens if you move your DimStaff.ResourceType = '2' to your JOIN statement?
Is not, are you able to provide some DDL and DLM (check the link in my signature).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 31, 2017 at 7:42 am
Thom A - Tuesday, January 31, 2017 7:22 AMStab in the dark, what happens if you move your DimStaff.ResourceType = '2' to your JOIN statement?Is not, are you able to provide some DDL and DLM (check the link in my signature).
That did it, so simple thank you!!!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply