Search query one way to return data another 3

[dalec]

Thank in advance!

I have a query where I'm searching an inventory database. This database has items grouped by categories (headers). What I'm trying to do is search only the descriptions from the categories (no problem), but, what I need returned is the items also under the categories searched . Can I use a sub-query to do that?

Here's my search query:

PROCEDURE Main_InvSch 

	@NameSch nvarchar(70)
as

SELECT * from Main_inventory

where charindex(@NameSch, [descrip]) > 0 and header = 1
order by [descrip]

Essentially I want the items and headers returned which would be the ones where header = 0 (but only the items of the ones returned).

Thanks,
Dale

 

[PWise]

How About

Select *
from Main_inventory Main
Where header = 1
inner join Main_inventory Items
on Items.descrip=main.descrip
and items.header =0
where charindex(@NameSch, [Main.descrip]) > 0

 

[dalec]

Except the items are in the same database with the categories. I just use a bit field to tell if it's header (category) or not.

 

[gmmastros]

Can you post some sample data and expected results?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dalec]

Yes, The data structure is like this:

Description nvchar 50
Qty
Cost
Category int
ID Identity
Header Bit

When an item is entered it can also be a Category Header (this is probably where my biggest hurdle is, since I used the same data base to store both header and items).

For Example:

Flex Duct <---- Header/Category
Flex Duct R6 <---- Item in Category Flex Duct
Flex Duct R8 <---- Item in Category Flex Duct

The users want to search by the category, but, return the category and all the items in within it. What I have been attempting is to seach the description of only headers, but that where I get stuck, I don't know exactly how to return the headers found with there underlying items.

I'm using the dataset returned in a bound datagrid, and was hoping there might be a sql solution with sub query's. I've never done one. I this is possible it will save some coding and would probably be much faster.

thanks in advance,
Dale

Expected results

 

[RyanEK]

declare @temp table (category varchar(15), header bit)
insert into @temp values ('Flex Duct', 1)
insert into @temp values ('Flex Duct R6', 0)
insert into @temp values ('Flex Duct R8', 0)

select t1.category, t2.*
from @temp t1
inner join (
  select * 
  from @temp
  where header = 0  
) t2 on charindex(t1.category, t2.category) = 1
where t1.header = 1 and t1.category = 'Flex Duct'

 

[dalec]

thank you Ryan

Could you explain this a little. I'm getting most of it, looking in the entire table, sub query on header = 0 and with a search on category where the category = the category of a temp table?

Sorry if I'm missing something, I've just not done query's like this.

thanks in advance.

 

[gmmastros]

The code that Ryan posted should probably work ok for you, but there are other ways too.

If your items (that are not headers) are all named like you show, then a simple like query should work.

Ex:

[tt][blue]
Select *
From Main_inventory
Where Description Like 'Flex Duct%'
[/blue][/tt]

I prefer to not rely on how things are spelled. As such, there is a derived table technique that you can use, based on the Category column.

To get the category for your item, the following query should work pretty well.

         Select Category
         From   Main_Inventory
         Where  Header = 1
                And Description = 'Flex Duct'

Now, if we can use this information to get everything else we want. To do this, we will make the previous query become a derived table. We will link our main table to this derived table such that the derived table will act like a filter.

Select *
From   Main_Inventory
       Inner Join (
         Select Category
         From   Main_Inventory
         Where  Header = 1
                And Description = 'Flex Duct'
         ) As AliasName
         On Main_Inventory.Category = AliasName.Category

Notice the original query embedded in this one. That's called a derived table, and often performs MANY times faster than a sub-query.

Also notice a couple other things. To make the derived table work, you need to put parenthesis around it. You also need to give this derived table an alias so that you can refer to it in other places.

I encourage you to learn hot to effectively use derived tables, it will make you a MUCH better t-sql developer.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[dalec]

Thank you, I'll play around with this in the SP. I've always used standard query's and was really hoping for SQL to do the work on this one. This really helps. Thank you for the great explanation too.

Dale

 

[dalec]

Here is what I ended up with:

PROCEDURE Main_InvSch 

	@NameSch nvarchar(70)
as

SELECT * from Main_inventory
   inner join {
       select category 
       from Main_Inventory
       where header = 1 and
             description = charindex(@NameSch, [descrip]) > 0
   } as CategoryQuery
on Main_Inventory.Category = CategoryQuery.Category

order by [descrip]


GO

I get a synax error access violation. I should note that the category is an int, it just points to the ID of the header item it belongs to.

Thanks in Advance

 

[dalec]

You know what, it might matter that it is SQL 2000

 

[jadams0173]

Could it be that the { } are suppose to the () per Georges example?

 

[dalec]

Thanks for that, don't know how I did that? Now I get a incorrect syntax near '>'

Current Code:

PROCEDURE Main_InvSch 

	@NameSch nvarchar(70)
as

SELECT * from Main_inventory
   inner join (
       select category 
       from Main_Inventory
       where [descrip] = charindex(@NameSch, [descrip]) > 0 
             and header = 1
             
   ) as CategoryQuery
on Main_Inventory.category = CategoryQuery.Category

order by [descrip]

 

[markros]

This is wrong

description = charindex(@NameSch, [descrip]) > 0

I'm guessing your where should be

where charindex(@NameSch, [descrip]) > 0 and Header = 1

But when why do you need charindex at all, should it be

[Descrip] like '%' + @NameSch + '%'

instead ?

 

[dalec]

That save correctly, thank you! I didnt know what to do.