Tricky select statement - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Tricky select statement

Author

Topic

cuthbert
Starting Member

5 Posts

Posted - 2007-05-24 : 02:02:40

Hi,

I have been messing around with this select statement for a couple of days now and I was hoping that someone can lend a hand.

I have two tables (itemtbl and suppitbl)

The 'itemtbl' table holds data like this:


item_no      parent
1111           0
2222           1111
3333           1111
4444           0
5555           4444
6666           4444

The 'suppitbl' table holds data like this:


item_no      supp_no
1111           98765
4444           43210

What I am trying to do is bring back a list of results that looks like this: (which displays each item no against its supplier no but if the item no has a parent then display the supplier no of the parent)


item_no      supp_no
1111           98765
2222           98765
3333           98765
4444           43210
5555           43210
6666           43210

So far I can display the result for one item number at a time using:


select
  itemtbl.item_no,
  case
    When itemtbl.parent = 0 then supitbl.supp_no
    Else (select supitbl.supp_no
          from supitbl, itemtbl
          where supitbl.item_no = itemtbl.item_parent and
                itemtbl.item_no = 2222)
  End as myCode
from itemtbl LEFT JOIN supitbl
ON itemtbl.item_no = supitbl.item_no
where itemtbl.item_no = 2222

...but I need to somehow display the results for more than one item at a time.

Thanks in advance
Glen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-05-24 : 02:14:12

[code]
select itemtbl.item_no,
case
When a.parent = 0 then b.supp_no
Else ( Select aa.supp_no
from supitbl aa
where aa.item_no = a.Parent)
End as myCode
from itemtbl a LEFT JOIN supitbl b
ON a.item_no = b.item_no

[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-24 : 02:14:46

[code]
select i.item_no, s.supp_no
from itemtbl i left join suppitbl s
on s.item_no = coalesce(nullif(i.parent, 0), i.item_no)
[/code]

Will you have more than 2 levels in itemtbl table ?

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-05-24 : 02:31:17

Select a.item_no, Parent = case when supp_no is null then parent else supp_no end
from @t a left outer join @y b
on a.parent = b.item_no or a.item_no = b.item_no

cuthbert
Starting Member

5 Posts

Posted - 2007-05-24 : 08:20:55

Hi,

Thanks everyone for your responses.
I have adapted your sql to look like this: (I have used my real table names to lessen confusion when trying to troubleshoot one issue)


select a.item_number, a.item_size, b.supi_supplier_no, a.item_desc, a.item_replicate,
  case when a.item_parent_number = 0 then b.supi_supplier_item
       else (select aa.supi_supplier_item
             from supitbl aa
             where aa.supi_item_number = a.item_parent_number and
                    a.item_desc like '%mySearchString%')
  end as myCode
from itemtbl a LEFT JOIN supitbl b
ON a.item_number = b.supi_item_number
where a.item_desc like '%mySearchString%' and
      a.item_desc not like 'BAR%'
order by a.item_replicate, a.item_number, a.item_desc asc

The above sql just about satisfies my needs (thanks again) except for one thing, the subquery can bring back more than 1 result because a supplier can have more than one supplier code for an item which gives me the below error:

Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (State:21000, Native Code: 200)

Does anyone have any ideas on how I can change my query so it can cater for many rows in the subquery??

Cheers
Glen

yumyum113
Starting Member

31 Posts

Posted - 2007-05-24 : 10:40:12

Probably the error is because there are item_no in itembl which have more than one kind of parent, thus leading to sub query

select aa.supi_supplier_item
from supitbl aa
where aa.supi_item_number = a.item_parent_number and
a.item_desc like '%mySearchString%'

to return more than one record.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-24 : 11:00:43

have you tried the query i posted ?

cuthbert
Starting Member

5 Posts

Posted - 2007-05-24 : 19:24:02

Hi KHTAN,

I tried your code again this morning (i tried it last night but it was late) and it brings the results back as needed. Thanks!

Thanks to everyone else who contributed.

For those that are interested my final query is as follows:


select i.item_number, i.item_size, i.item_desc, i.item_replicate, s.supi_supplier_item, s.supi_supplier_no
from itemtbl i left join supitbl s
on s.supi_item_number = coalesce(nullif(i.item_parent_number, 0), i.item_number)
where i.item_desc like '%mySearchString%' and
i.item_desc not like 'BAR%'
order by i.item_replicate, i.item_number, i.item_desc asc

Cheers
Glen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources