July 31, 2017 at 3:22 pm
I have two tables - prod table shows employees and prod id that they are allowed to sell.
Inv table has invoice numbers and the emloyee name and product.
Each employee eligible to see must be connected to that invoice.
I want to compare these two tables to find which inv rows an eligible employee is missing.
The result I get from the compare query below is ok except that it does not include invoice 104 for prod 11 and employee b.
The correct answer shoud be
101 c 11
102 d 12
104 b 11
104 c 11
Can anyonehelp with the sql to make that work? Thanks much in advance.
create table #prod
(
nameID int
, name nvarchar(255)
, prod nvarchar(255)
)
insert into #prod (nameID, name, prod)
values(1, 'a', 11)
insert into #prod (nameID, name, prod)
values(2, 'b', 11 )
insert into #prod (nameID, name, prod)
values(3, 'c', 11)
insert into #prod (nameID, name, prod)
values(2, 'b', 12)
insert into #prod (nameID, name, prod)
values(4, 'd', 12)
insert into #prod (nameID, name, prod)
values(3, 'c', 13)
insert into #prod (nameID, name, prod)
values(4, 'd', 13)
create table #inv
(
inv_id int
, name nvarchar(255)
, prod nvarchar(255)
)
insert into #inv (inv_id, name, prod)
values(101, 'a', 11)
insert into #inv (inv_id, name, prod)
values(101, 'b', 11 )
insert into #inv (inv_id, name, prod)
values(102, 'b', 12)
insert into #inv (inv_id, name, prod)
values(102, 'c', 12)
insert into #inv (inv_id, name, prod)
values(103, 'c', 13)
insert into #inv (inv_id, name, prod)
values(104, 'a', 11)
select *
from #prod p full outer join #inv i on
p.name = i.name
and p.prod = i.prod
where i.prod is null
or p.name is null
or p.prod is null
or i.name is null
nameID name prod inv_id name prod
3 c 11 NULL NULL NULL
4 d 12 NULL NULL NULL
4 d 13 NULL NULL NULL
NULL NULL NULL 102 c 12
July 31, 2017 at 4:16 pm
Something like this should do the trick to create the missing #inv rows, if I understand your request correctly:
SELECT i.inv_id,p.name,p.prod
FROM #prod p INNER JOIN #inv i on p.prod=i.prod
GROUP BY i.inv_id,p.name,p.prod
HAVING MAX(CASE WHEN p.name=i.name THEN 1 ELSE 0 END)=0;
On that note, though, it would probably make more sense to only store inv_id and prod values in the inv table. Then if you wanted to see eligible employees, it's a simple inner join between prod and inv.
Fewer chances of missing things, and less duplication. In a vacuum, and based solely on what you've shown in your post, I would prefer that design, but there could be a number of reasons that's not feasible or best in your case.
Cheers!
August 1, 2017 at 5:02 am
It does not seem to find the missing rows in my real data.
As for the design, this is what I have inherited so that's what it is.
Thanks.
August 1, 2017 at 5:06 am
tinausa - Tuesday, August 1, 2017 5:02 AMIt does not seem to find the missing rows in my real data.As for the design, this is what I have inherited so that's what it is.
Thanks.
Which rows is it missing? If there are scenarios that the code Jacob supplied is missing, can you provide sample data of those scenarios, so we can work out how to get around it?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
August 1, 2017 at 7:37 am
I just put a criteria for an inv_id. And it is including all employees in the result.
I just check for inv_id 104 and it gives me all employees.
August 1, 2017 at 8:16 am
As Thom said, if you could give us sample data that recreates the issue that would be ideal.
Also, what is the exact query you ran?
Cheers!
August 1, 2017 at 9:04 am
Thanks both it worked, I had to change the join on employee rather than prod.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply