tselectro
asked on
String search in two linked tables
Hi.
I have two tables.
CREATE TABLE [dbo].[Pages](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageName] [varchar](50) NOT NULL
) ON [PRIMARY]
AND
CREATE TABLE [dbo].[Pages_ObjectCrossRe f](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Pages_ID] [int) NOT NULL,
[ObjectName] [varchar](50) NOT NULL,
) ON [PRIMARY]
I want to create a sql statement that "search" in the PageName column (Pages table) and in the ObjectName field (Pages_ObjectCrossRef table). In additional I want to get info if the "search" only match the ObjectName.
Ex.
Pages:
ID PageName
1 Test page1
2 Test page2
Pages_ObjectCrossRef :
ID Pages_ID ObjectName
1 1 Object 1
3 1 Object 2
4 2 Object 1
8 2 Object 3
9 2 Test Object 4
SELECT DISTINCT TOP (10) PagesX.ID, PagesX.PageName
FROM PagesX INNER JOIN PagesX_ObjectCrossRef ON PagesX.ID= PagesX_ObjectCrossRef.Page s_ID
WHERE
PagesX.PageName LIKE '%test%' OR
PagesX_ObjectCrossRef.Obje ctName LIKE '%test%'
ORDER BY PagesX.PageName
RESULT
1 Test page1
2 Test page2
What I want (extra info if the "search" match ObjectName):
ID Page ObjectMatch
1 Test page1 0
2 Test page2 1
Any suggestion?
I have two tables.
CREATE TABLE [dbo].[Pages](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PageName] [varchar](50) NOT NULL
) ON [PRIMARY]
AND
CREATE TABLE [dbo].[Pages_ObjectCrossRe
[ID] [int] IDENTITY(1,1) NOT NULL,
[Pages_ID] [int) NOT NULL,
[ObjectName] [varchar](50) NOT NULL,
) ON [PRIMARY]
I want to create a sql statement that "search" in the PageName column (Pages table) and in the ObjectName field (Pages_ObjectCrossRef table). In additional I want to get info if the "search" only match the ObjectName.
Ex.
Pages:
ID PageName
1 Test page1
2 Test page2
Pages_ObjectCrossRef :
ID Pages_ID ObjectName
1 1 Object 1
3 1 Object 2
4 2 Object 1
8 2 Object 3
9 2 Test Object 4
SELECT DISTINCT TOP (10) PagesX.ID, PagesX.PageName
FROM PagesX INNER JOIN PagesX_ObjectCrossRef ON PagesX.ID= PagesX_ObjectCrossRef.Page
WHERE
PagesX.PageName LIKE '%test%' OR
PagesX_ObjectCrossRef.Obje
ORDER BY PagesX.PageName
RESULT
1 Test page1
2 Test page2
What I want (extra info if the "search" match ObjectName):
ID Page ObjectMatch
1 Test page1 0
2 Test page2 1
Any suggestion?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.