Link to home
Start Free TrialLog in
Avatar of tselectro
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_ObjectCrossRef](
      [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.Pages_ID
WHERE
PagesX.PageName LIKE '%test%' OR
PagesX_ObjectCrossRef.ObjectName 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?
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial