如何在多个表中搜索项目 [英] How can I search for an item in multiple table

问题描述

最近我进入电子商务应用程序，在这里我想根据类别名称或子类别名称或产品名称搜索产品。在这里，我打算为类别，子类别和产品创建表格。如果我搜索项目，可以是类别名称或子类别名称或产品名称，则应显示产品。



例如。 Men-Shirt-Peterengland / Levis / john播放器/



如果我搜索文本'衬衫'，应显示所有产品（Peterengland / Levis / john player /） 。如果我搜索perterengland，只显示该产品。如果我使用主要类别搜索所有子类别，则应显示此子类别下的所有产品。



如果有人对此有所了解，请帮助我。

Recently i am into E-commerce application, here i want to search the products according to category name or sub category name or product name. here i am planning to create table for Category, subcategory and products. If i search for an item, that can be category name or sub category name or product name, accordingly the products should be displayed.

Eg. Men-Shirt-Peterengland/Levis/john player/

If i search the text 'shirt' all the products(Peterengland/Levis/john player/) should be displayed. If i search perterengland ,only that product to be displayed. If i search with the main category all the subcategories and all products under this sub categories should be displayed.

Help me if any one have an idea on it.

推荐答案

首先，不要为类别创建两个表。当您的雇主/买家/客户想要另一个子级别时会发生什么？



创建表格列，其中包含列ID，名称，等等，其他，需要和CRUCIALLY parent_id将在同一个表中包含对id列的引用。



这样你可以根据需要轻松扩展所需的级别。



第二个好处是你不必加入三个表，只需要两个：

（你在搜索框中发送的参数中的@search）

First of all, do NOT create two tables for categories. What will happen when your employer / buyer / client wants another sub-level?

Create table Categories with columns id, name, whatever, else, needed AND CRUCIALLY parent_id which will contain reference to id column in the same table.

This way you can have as many levels as needed easily extensible.

Second advantage is that you don't have to join three tables, but only two:
(@search in parameter you send from your search box)

SET @search = '%' + ISNULL(@search, '') + CASE WHEN ISNULL(@search, '') = '' THEN '' ELSE '%'
SELECT * FROM Categories c inner join Products p on p.category_id = c.id
WHERE category_name like @search OR p.product_name LIKE @search OR product_code like @search

e tc ...对于你想要的多个字段



如果有空类别，你可以将INNER JOIN更改为左连接，如果有没有产品，你可以将完全外连接更改为类别等......

etc...for as many fields as you like

You can change INNER JOIN to left join if there are empty categories, or full outer join if there are products without categories etc...

create table tbl_Product
(id bigint primary key identity,category nvarchar(max),subcategory nvarchar(max),product nvarchar(max))

insert into tbl_Product values('shirt','PeterEng','../image/1.jpg')

insert into tbl_Product values('shirt','Levis','../image/2.jpg')

insert into tbl_Product values('T-shirt','PeterEng','../image/3.jpg')

insert into tbl_Product values('shirt','Levis','../image/4.jpg')


//Pass the Values to Stored Procedure from C#



create procedure sp_product
@category nvarchar(max)=null,
@subcategory nvarchar(max)=null,
as
begin
declare @sql nvarchar(max);
if @category!='' && @subcategory=''
set @sql=@sql+'select product,subcategory from tbl_product'

if @category!='' && @subcategory=!''
set @sql=@sql+'select product from tbl_product'

set @sql
end

示例来自R @ jes的表＃

example Table from R@jes#

create table tbl_Product
(id bigint primary key identity,category nvarchar(max),subcategory nvarchar(max),product nvarchar(max))
 
insert into tbl_Product values('shirt','PeterEng','../image/1.jpg')
 
insert into tbl_Product values('shirt','Levis','../image/2.jpg')
 
insert into tbl_Product values('T-shirt','PeterEng','../image/3.jpg')
 
insert into tbl_Product values('shirt','Levis','../image/4.jpg')

只需使用OR条件，像运算符，通配符

你可以得到它

Just Use OR Condition ,Like Operator ,wildcards
You can Get it

select *from tbl_Product where category like 'Levis%' or subcategory like 'Levis%'

不要Expec这里有确切的答案。得到一个想法。试试你自己：）

Do not Expect Exact Answer here. Just get an Idea.Try Your self :)

这篇关于如何在多个表中搜索项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！