Hi.

I need some help here with a multi-table join.
I am writing a stored procedure with a code block that
needs to join my service table, my product table, and
my #tmp table (used to sort categories) - returning all
product names and service names that satisfy a keyword
string as one column and another (related column) to designate the
item type (product or service).

so, in pseudo(ish) code, something to the effect:

create table #tmp(names, type);

select servicename & productname as names
(product or service) as type
from service s, product p, #temp t
where s.categoryid = t.catid
and t.catid = p.categoryid
and freetext(s.servicename, @UsrKeywords)
and freetext(p.productname, @UsrKeywords);

I know the above is bad SQL but I hope it gives you a
rough idea of what I am trying to do. Also, there is no
column designating "product or service" but I am wondering
if it is possible to itemize the type column based on what column
the "names" data is being pulled from?

Thanks in advance for any help / guidance offered.

NOW, MY TABLES:

TEMP TABLE CREATED IN STORED PROCEDURE:

CREATE TABLE #tmp(catid INT,
probe INT)

INSERT INTO #tmp(catid, probe)
SELECT categoryid, 0
FROM category c
WHERE categoryname in (@CategoryName)

MY CATEGORY TABLE:

CREATE TABLE [dbo].[Category](
[categoryid] [int] IDENTITY(1,1) NOT NULL,
[categoryparentid] [int] NULL,
[categoryname] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[categoryid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [MyDBDBDev]
GO
ALTER TABLE [dbo].[Category] WITH CHECK ADD CONSTRAINT
[FK_Category_Category] FOREIGN KEY([categoryid])
REFERENCES [dbo].[Category] ([categoryid])

PRODUCT TABLE:

CREATE TABLE [dbo].[Product](
[productid] [int] IDENTITY(1,1) NOT NULL,
[categoryid] [int] NOT NULL,
[userid] [int] NOT NULL,
[productname] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[productdescription] [text] COLLATE Latin1_General_CI_AI NULL,
[productprice] [money] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[productid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [MyDBDBDev]
GO
ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT
[FK_Product_Product] FOREIGN KEY([productid])
REFERENCES [dbo].[Product] ([productid])

MY SERVICE TABLE:

CREATE TABLE [dbo].[Service](
[serviceid] [int] IDENTITY(1,1) NOT NULL,
[categoryid] [int] NOT NULL,
[userid] [int] NOT NULL,
[servicename] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[servicedescription] [text] COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_Service] PRIMARY KEY CLUSTERED
(
[serviceid] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
USE [MyDBDBDev]
GO
ALTER TABLE [dbo].[Service] WITH CHECK ADD CONSTRAINT
[FK_Service_Category] FOREIGN KEY([categoryid])
REFERENCES [dbo].[Category] ([categoryid])

pbd22 (dushkin (AT) gmail (DOT) com) writes:
It may be that it is too early in the morning, and I'm still waiting
for my tea to get ready, but the way you've written the pseudo-query,
will not the answer be both tables?

If you change the WHERE clause to:

where s.categoryid = t.catid
and t.catid = p.categoryid
and (freetext(s.servicename, @UsrKeywords) or
freetext(p.productname, @UsrKeywords));

I can understand your question.

I think the best is to run a UNION ALL query, one where you search the
service name and one on product name. And obviously each part of the
UNION only needs to involve two tables not three.



--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx