April 29, 2014 at 4:15 pm
I will try to explain the best I can, but this query is getting the best of me. I have table 'stores' that has 3 columns (storeid, article, doc), I have a second table 'allstores' that has 3 columns(storeid(always 'ALL'), article, doc). The stores table's storeid column will have a stores id, then will have multiple articles, and docs. The 'allstores' table will have 'all' in the store for every article and doc combination. This table is like the master lookup table for all possible article and doc combinations. The 'stores' table will have the actual article and doc per storeid.
What I am wanting to pull is all article, doc combinations that exist in the 'allstores' table, but do not exist in the 'stores' table, per storeid. So if the article/doc combination exists in the 'allstores' table and in the 'stores' table for storeid of 50 does not use that combination, but store 51 does, I want the output of storeid 50, and what combination does not exist for that storeid. I will try this example:
'allstores' 'Stores'
storeid doc article storeid doc article
ALL 0010 001 101 0010 001
ALL 0010 002 101 0010 002
ALL 0011 001 102 0011 002
ALL 0011 002
So I want the query to pull the one from 'allstores' that does not exist in 'stores' which in this case would the 3rd record "ALL 0011 001"
I have writen many variations of what I thought was right, and cannot figure it out.. I appreciate any help.
April 29, 2014 at 4:32 pm
I'm not sure that I understand exactly what you want. First of all, it's nonsense to store a column with a value that will be the same for all rows.
Second, you should post your data in a consumable way including DDL and Insert statements so we don't need to spend time creating the scenario. You can read about how to do it in the article linked in my signature. I used a CTE to simulate the data this time because you seem new to the forum.
Here's a possible solution, but I'm not sure if it's what you're looking for.
WITH allstores(storeid, doc, article) AS(
SELECT 'ALL', '0010', '001' UNION ALL
SELECT 'ALL', '0010', '002' UNION ALL
SELECT 'ALL', '0011', '001' UNION ALL
SELECT 'ALL', '0011', '002'
),
Stores(storeid, doc, article) AS(
SELECT 101, '0010', '001' UNION ALL
SELECT 101, '0010', '002' UNION ALL
SELECT 102, '0011', '002'
)
SELECT s.storeid
,a.doc
,a.article
FROM allstores a
CROSS
JOIN (SELECT DISTINCT storeid FROM Stores) s
EXCEPT
SELECT s.storeid
,s.doc
,s.article
FROM Stores s
May 13, 2014 at 8:14 am
Luis - first of all - Thanks so much for the solution! Secondly - you are correct, I don't post much to the forum and usually have pretty good luck on figuring these things out on my own, but this one really had me stumped. I appreciate your time, and will also remember to include the ddl to make it easier on the reader to help.
Have a great day, and I appreciate your help.
Brian
May 13, 2014 at 8:20 am
You're welcome. Thank you for the feedback.
I hope that you understand how the solution works. If not, feel free to ask any questions that you have.
May 13, 2014 at 8:23 am
I was able to manipulate it a bit into my scenario, and worked Perfectly!! Then added to the rest of the solution, and everyone is happy! Thanks again!!
May 13, 2014 at 4:43 pm
I don't think you actually need to go thru all those gyrations; instead, try this:
;WITH allstores(storeid, doc, article) AS(
SELECT 'ALL', '0010', '001' UNION ALL
SELECT 'ALL', '0010', '002' UNION ALL
SELECT 'ALL', '0011', '001' UNION ALL
SELECT 'ALL', '0011', '002'
),
Stores(storeid, doc, article) AS(
SELECT 101, '0010', '001' UNION ALL
SELECT 101, '0010', '002' UNION ALL
SELECT 102, '0011', '002'
)
SELECT a.doc, a.article
FROM allstores a
WHERE
NOT EXISTS(
SELECT 1
FROM Stores s
WHERE
s.doc = a.doc and
s.article = a.article
)
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 14, 2014 at 7:41 am
Well done Luis, I haven't had the opportunity to use the Cross Join, but I will remember it for any future needs. With not knowing about the cross join I was wondering how I would have done this. Here is what I came up with, but I like the cross join better.
WITH allstores(storeid, doc, article) AS(
SELECT 'ALL', '0010', '001' UNION
SELECT 'ALL', '0010', '002' UNION
SELECT 'ALL', '0011', '001' UNION
SELECT 'ALL', '0011', '002'
),
Stores(storeid, doc, article) AS(
SELECT 101, '0010', '001' UNION
SELECT 101, '0010', '002' UNION
SELECT 102, '0011', '002' UNION
SELECT 103, NULL, NULL
)
SELECT s.storeid
,a.doc
,a.article
FROM (SELECT DISTINCT storeid FROM Stores) s
LEFT JOIN allstores a ON
a.storeid = 'ALL'
EXCEPT
SELECT s.storeid
,s.doc
,s.article
FROM Stores s
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
May 14, 2014 at 8:40 am
Scott,
Your query returns a different result. That might be what the OP was looking or it might not. I'm not sure. I agree that it would be a better solution if that was the desired solution.
Below86,
You might not realize it, but you created a cross join as well by not making any relationship between the two tables. However, it makes a very complicated execution plan with the CTE but it shows the same plan if we use temp tables. You'll notice there's a warning saying there's no join predicate, which indicates the cross join.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply