T-SQL query for different data in fields
Hello
Need assistance writing a query which only returns instances where the Job_Id is the same, but the Product_Id and Type are different.
*See attachment.
Jobs_EX.xlsx
Need assistance writing a query which only returns instances where the Job_Id is the same, but the Product_Id and Type are different.
*See attachment.
Customer JoB_ID Product_Id Type
ABC Movers 33225 1775 RE
ABC Movers 33225 1776 Non_RE
XYZ Ltd 33001 1775 RE
XYZ Ltd 33001 1775 REJobs_EX.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@HainKurt the expected result is the one highlighted in yellow. Thanks.
maybe this:
with dups as (
select Job_id
from jobs j
group by Job_id
having count(distinct Product_id)>1
)
select * from jobs j inner join dups d on j.job_id=d.job_idSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The question states: Job_Id is the same, but the Product_Id and Type are different.
I missed that part :)
we can use this, which is almost same as my previous query
with jobs as (
select 'ABC Movers' Customer, 33225 JoB_ID, 1775 Product_Id, 'RE' [Type]
union all select 'ABC Movers', 33225, 1776, 'Non_RE'
union all select 'XYZ Ltd', 33001, 1775, 'RE'
union all select 'XYZ Ltd', 33001, 1775, 'RE'
), dups as (
select Job_id
from jobs j
group by Job_id
having count(distinct concat(Product_id,Type))>1
)
select * from jobs j inner join dups d on j.job_id=d.job_id
Customer JoB_ID Product_Id Type Job_id
ABC Movers 33225 1776 Non_RE 33225
ABC Movers 33225 1775 RE 33225
if you missed that part and someone else has addressed it, perhaps it might be allowed that others get to answer a question occasionally?
(even if one can keep inventing syntax variants so it isn't identical)
(even if one can keep inventing syntax variants so it isn't identical)
Please try full and tested solution - Simple solution
Solution
Output
CREATE TABLE Customers
(
[Customer] varchar(10), [JoB_ID] int, [Product_Id] int, [Type] varchar(6)
)
GO
INSERT INTO Customers
([Customer], [JoB_ID], [Product_Id], [Type])
VALUES
('ABC Movers', 33225, 1775, 'RE'),
('ABC Movers', 33225, 1776, 'Non_RE'),
('XYZ Ltd', 33001, 1775, 'RE'),
('XYZ Ltd', 33001, 1775, 'RE')
GOSolution
SELECT Customer , JoB_ID , Product_Id , [Type] FROM
(
SELECT * , COUNT(*) OVER(PARTITION BY JoB_ID,[Type],Customer) cnt FROM Customers
)r WHERE cnt = 1Output
/*------------------------
SELECT Customer , JoB_ID , Product_Id , [Type] FROM
(
SELECT * , COUNT(*) OVER(PARTITION BY JoB_ID,[Type],Customer) cnt FROM Customers
)r WHERE cnt = 1
------------------------*/
Customer JoB_ID Product_Id Type
---------- ----------- ----------- ------
ABC Movers 33225 1776 Non_RE
ABC Movers 33225 1775 RE
(2 row(s) affected)
I'd urge you to go with PortletPaul's method, since presumably you want to list more than 2 non-matches if they exist.
The CONCAT method is inherently unreliable (aside from the likely poor overall performance). Consider:
| ABC Movers | 33225 | 1775 | RE |
| ABC Movers | 33225 | 177 | 5RE |
The CONCAT method is inherently unreliable (aside from the likely poor overall performance). Consider:
| ABC Movers | 33225 | 1775 | RE |
| ABC Movers | 33225 | 177 | 5RE |
The CONCAT method is inherently unreliable (aside from the likely poor overall performance).
we can solve it by
having count(distinct concat(Product_id,'|',Type))>1if it is really an issue :)
Unless a vertical bar happens to appear in the data. If for some bizarre reason one insisted on using an inherently inefficient CONCAT method to do this task, you should use a character such as CHAR(7) (bell), which is almost certain not to appear in any data.
why this is not highlighted?
XYZ Ltd 33001 1775 RE
XYZ Ltd 33001 1775 RE