Recursive queries(not a parent/child tree)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive queries(not a parent/child tree)

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-17 : 18:55:37

Omar writes "I have one table (machine) as such:

Machine_Code
Part_No

There are many Machine Names and many Part Numbers, but a machine with certain parts is a unique machine. I need to be able to get a list of machines that contains a list of part numbers (e.g. get all machine codes that have the following part numbers: 123, 345, 234, 567, 568). The list of part numbers could be up to 50. I can do this with a recursive query, but there is a limit on how many aliases there can be. Can you help with this? This is what I have tried and has worked:

select a.*
from machine a,
machine b,
machine c,
machine d,
machine e
where a.machine_code = b.machine_code
and a.machine_code = c.machine_code
and a.machine_code = d.machine_code
and a.machine_code = e.machine_code
and a.part_no = '123'
and a.part_no = '345'
and a.part_no = '234'
and a.part_no = '567'
and a.part_no = '568';

The problem is when I have 50 part numbers. I exceed the number of table joins.

Thanks for your help.

OS: Windows 2000
SQL Server: 2000"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-17 : 19:14:33

I think there is an error, because this query can't return any rows. All of the part number values are ANDed together, which is logically impossible to satisfy.

If you repost, please include the FULL structure of the machine table, some sample data, and the EXACT results you want.

ocolmena
Starting Member

2 Posts

Posted - 2002-05-20 : 09:31:11

You are right. My mistake. Here is the corrected query.

select a.*
from machine a,
machine b,
machine c,
machine d,
machine e
where a.machine_code = b.machine_code
and a.machine_code = c.machine_code
and a.machine_code = d.machine_code
and a.machine_code = e.machine_code
and a.part_no = '123'
and b.part_no = '345'
and c.part_no = '234'
and d.part_no = '567'
and e.part_no = '568';

Some sample data:

machine_code part_no
1 123
1 345
1 234
1 567
1 568
2 123
3 345
4 234
5 567

The query should only return machine_code 1.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-20 : 09:40:54

Let me ask this another way: you have a list of part numbers, and you want the machines that have ALL of those part numbers? Is that correct? I think you can do that without any joins at all (well, 1 or 2 at most)

ocolmena
Starting Member

2 Posts

Posted - 2002-05-20 : 10:38:29

Robvolk:

You got it right. That is exactly what I need.

Thanks.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-20 : 11:32:04

This is known as relational division, which I know because Joe Celko was kind enough to describe it:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=14718

The following is an elaboration on the queries Joe presented; it's a stored procedure that accepts a comma-delimited string of part numbers, parses them out, and gives you the results you need:

CREATE PROCEDURE FindAllParts @parts varchar(8000) AS
set nocount on
SELECT @parts=',' + @parts + ','

SELECT Convert(int, Substring(@parts, seq, charindex(',' , @parts, seq)-seq)) Part
INTO #parts FROM Sequence
WHERE seq<=len(@parts) AND Substring(@parts , seq - 1, 1) = ','

SELECT M.Machine_code FROM machine M INNER JOIN #parts P ON M.part=P.Part
GROUP BY M.Machine_code
HAVING Count(P.Part)=(SELECT count(Part) FROM #parts)

DROP TABLE #parts

Take a look at this article:

http://www.sqlteam.com/item.asp?ItemID=2652

It describes how the parsing code works, I modified it only a little bit here (table named Sequence instead of Tally, column named seq instead of ID) The main thing is that you need to create a Sequence (or Tally) table and populate it with values first. Once you do then this code will work fine, like this:

EXECUTE FindAllParts '123,345,234,567,568'

I *think* this could be done without the temp tables, but the query would look really ugly, and you won't be able to parse enough parts for a temp table to cause performance problems. One thing to keep in mind is that varchar maxes out at 8000 characters, so if you have very long part numbers (10 digits each) you'll be limited to about 650 parts in the list. Should be long enough

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-20 : 11:38:38


create table #blueprint (machine_code int, part_no int)
insert #blueprint
select 1, 123 union
select 1, 345 union 
select 1, 234 union
select 1, 567 union
select 1, 568 union
select 2, 123 union
select 3, 345 union
select 4, 234 union
select 5, 567

create table #partslist(part_no int)
insert #partslist
select 123 union
select 345 union
select 234 union
select 567 union
select 568
go

select
    b.machine_code
from
    #partslist p
    inner join #blueprint b
        on p.part_no = b.part_no
where
    not exists (
        select 1
        from
            #blueprint b2
        where
            b.machine_code = b2.machine_code and
            b2.part_no not in (select
                                   part_no
                               from
                                   #partslist))
group by
    b.machine_code
having
    count(distinct b.part_no) = (select
                                     count(47)
                                 from
                                     #partslist)
go
drop table #blueprint
drop table #partslist

I've been sniped, but I am gonna post this anyway. This is essentially the same, except this will exclude those machines that have all the parts in the list, but also have parts not in the list . . . it depends if you want an exact match on parts or if the list of part numbers is allowed to be a subset of the total family of parts on the machine . . .

<O>

edit by robvolk - to fix the display
edit by Page47 - to undo robvolks fix and fix it my way :)

Edited by - robvolk on 05/20/2002 11:40:01

Edited by - Page47 on 05/20/2002 11:42:09

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources