October 19, 2011 at 2:06 pm
Simple 3 table many to many relation:
Tax Table( TaxPK CHAR(16) , othercolumns...)
Inventory Table( InventoryPK CHAR(16), othercolumns...)
TaxApplies Table( TaxPK CHAR(16), InventoryPK CHAR(16))
Simple enough to list the items to which each tax applies, but...
For auditing purposes, users would like a listing of any inventory items that are NOT listed in the TaxApplies table by tax and inventory. Presumably so they can easily scan for items that don't have taxapplies rows and should. Of course, only some should. Of course, the PK's aren't sequential numbers so I can't use a tally table. 😛
Ideas for building a query that takes less than the age of the universe (so I exaggerate) to run?
Of course, if this thread goes on too long, I'll probably just figure out another way to solve the audit problem, like listing inventory items and the taxes that apply so they can scan the list looking for any blanks where there shouldn't be.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
October 19, 2011 at 2:14 pm
Seems like a simple OUTER JOIN or EXCEPT to me, if I understand the problem correctly. Something like:
Select
I.*
From
Inventory AS I LEFT JOIN
TaxApplies AS TA
ON I.InventoryPK = TA.InventoryPK
Where
TA.InventoryPK Is Null
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 20, 2011 at 7:27 am
Oh, here we go:
select tax.taxpk, inventory.inventorypk
from tax
join inventory on 7=7
left outer join taxapplies on taxapplies.taxpk = tax.taxpk and taxapplies.inventorypk = inventory.inventorypk
where taxapplies.inventorypk is null
I was having trouble getting a join with the cross product, till I thought to put a constant in the "on" clause of inventory
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
October 20, 2011 at 8:34 pm
I'm not sure why you need the tax table. There is no relationship between inventory and tax without the taxapplies table, so the simple query I provided would show any inventory items that don't have a tax assigned. With your query you are getting a Cartesian product of inventory and tax so you'll get every possible combination. If that's what you want then you are in good shape with your query although it might be easier if you just used a CROSS JOIN then you don't need an ON.
SELECT
I.*
FROM
Inventory AS I
CROSS JOIN taxes AS T
LEFT JOIN TaxApplies AS TA
ON I.InventoryPK = TA.InventoryPK AND
T.taxpk = TA.taxpk
WHERE
TA.InventoryPK IS NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 21, 2011 at 7:42 am
Because they wanted to know what inventory items apply or don't apply by tax.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply