Late in the day query help requested
-
I have a table, machine_products (machine_id char(1), product_key int) Both columns make up the PK.
Data could resemble:
machine_id product_key
1 1
1 5
1 15
2 1
2 2
2 15
2 17
I need to assemble a query that will show me which product keys are available to be assigned to the machine not already assigned in the table. The example data results would return something like this:
machine_id product_key
1 2
1 17
2 5
I'm stumped on this - can anyone assist?
Thanks!
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
-
How about making a table of possible product keys? Something along those lines?
Andy
-
Hope this helps(and I hope this results in what was needed) - this is a really late in the day query reply....
note : *** is the plus sign...I can't seem to put the plus sign the text here...(???)...
select distinct(F2.machine_id),F1.product_key
from Forum F1,Forum F2 where
cast(F2.machine_id as varchar)***'#'***cast(F1.product_key as varchar)
not in
(
select cast(machine_id as varchar)***'#'***cast(product_key as varchar)
from Forum
)
Edited by - winash on 04/28/2003 9:29:22 PM
-
whoops...error in the query - alias for the table should be F1 instead of F...
select distinct(F2.machine_id),F1.product_key
from Forum F1,Forum F2 where
cast(F2.machine_id as varchar)***'#'***cast(F1.product_key as varchar)
not in
(
select cast(machine_id as varchar)***'#'***cast(product_key as varchar)
from Forum
)
Managing senior programmers is like herding cats.
-
This is the way I would normally go about it.
select sqPMT.*
FROM
(select *
from
(SELECT distinct machine_id from tblForum) as sqMchs
cross join
(select distinct product_key from tblForum) AS sqKeys) as sqPMT
LEFT JOIN
tblForum
ON
sqPMT.machine_id = tblForum.machine_id AND
sqPMT.product_key = tblForum.product_key
WHERE
tblForum.machine_id IS NULL
the subquery sqPMT generates a permutations table of all possiblities and by left joining it to the current items and looking for gaps with IS NULL we can find the missing items. Not sure if mine or winash's works better or if one works better at a specific amount of data so you should test to see which works best for you. There might still be other ways but if you could at least get a listing of all product_keys into a table permanently it can help performance as the table grows.
-
Winash, would you please explain? For some reason I can't figure out what the three *'s in the following statement means:
select distinct(F2.machine_id),F1.product_key
from Forum F1,Forum F2 where
cast(F2.machine_id as varchar)***'#'***cast(F1.product_key as varchar)
not in
(
select cast(machine_id as varchar)***'#'***cast(product_key as varchar)
from Forum
)
I don't suppose it should have been coded like this:
select distinct(F2.machine_id),F1.product_key
from Forum F1,Forum F2 where
cast(F2.machine_id as varchar) + '#' + cast(F1.product_key as varchar)
not in
(
select cast(machine_id as varchar) + '#' + cast(product_key as varchar)
from Forum
)
I also did a performance test between winash's example, and antares686. My simple test proved antares686 was more efficient. Here is my simple test:
set nocount on
create table forum (machine_id int, product_key int)
declare @i int
declare @st datetime
set @i = 0
while @i < 100
begin
insert into forum values(1,1 + @i)
insert into forum values(1,2 + @i)
insert into forum values(1,3 + @i)
insert into forum values(2,1 + @i)
insert into forum values(2,2 + @i)
insert into forum values(2,4 + @i)
insert into forum values(3,2 + @i)
set @i = @i + 1
end
select @st = getdate()
select sqPMT.*
FROM
(select *
from
(SELECT distinct machine_id from Forum) as sqMchs
cross join
(select distinct product_key from Forum) AS sqKeys) as sqPMT
LEFT JOIN
Forum
ON
sqPMT.machine_id = Forum.machine_id AND
sqPMT.product_key = Forum.product_key
WHERE
Forum.machine_id IS NULL
select datediff(ms,@st,getdate())
select distinct(F2.machine_id),F1.product_key
from Forum F1,Forum F2 where
cast(F2.machine_id as varchar) + '#' + cast(F1.product_key as varchar)
not in
(
select cast(machine_id as varchar) + '#' + cast(product_key as varchar)
from Forum
)
select datediff(ms,@st,getdate())
select @st = getdate()
drop table forum
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
Hi Greg,
the 3 stars are the plus sign - for some reason the plus sign doesnt appear in my machine here...
and Antares' query is faster...why am I not surprised...
-
Thank you all for your ideas and solutions - my problem is no more.
I find it wonderful that this forum is available.
Steve Armistead,
Database Administration
Panther Systems Northwest, Inc.
Vancouver, WA
Steve
-
quote:
Hi Greg,the 3 stars are the plus sign - for some reason the plus sign doesnt appear in my machine here...
and Antares' query is faster...why am I not surprised...
I appreciate the vote of confidence but I hit some solutions by mistake, some by repeated trys, some by readin others concepts, and some by playing with SQL server to figure what it is trying to do (and have to play again each new version). I like the forums as there is so much experience to draw from here that I now read the forums I don't even respond to just to see other possibilities in what I know. All I can say is keep learning, experimenting and posting. Oh, and test all solutions you can think of even thou one usually works fastest under one condition, those conditions may be totally differen in another challenge.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply