Search Multiple tables

Question

Hi All,

I have 4 tables in my SQL server database.

1. Country has field: Country_Name

2. State has field: State Name (FK – Country_ID)

3. City has field: City Name (FK- State_ID)

Plus

4. Level (Country (1), State (2), and City (3))

Country_ID	Country_Name	Level_ID
1	India	1
2	England	1
3	Ireland	1
…

State_ID	State_Name	Country_ID	Level_ID
1	Delhi	1	2
2	Maharashtra	1	2
…

City_ID	City_Name	State_ID	Level_ID
1	Pune	2	3
2	Mumbai	2	3
…

Now what I want to do is, I have a search on my website, when user enters any keyword in the search box and hit button; it should search for the title in tables 1, 2, and 3 and should return single result set along with the level.

Eg.

Search string	Required output
Ind	India(Country)
Mum	India- Maharashtra-Mumbai(City)
Del	India-Delhi(State)
land	England(Country) Ireland(Country)

How can I achieve this using sql?

Answer 1

Hi,

Please find the belwo query which may help a per ur requirement

Declare @Country Table
(
CountryId Int,
CountrName Varchar(30),
LevelId Int
)
Declare @State Table
(
StateID Int,
StateName Varchar(30),
CountryId Int,
LevelId Int
)
Declare @City Table
(
CityId Int,
CityName Varchar(30),
StateId Int,
LevelId Int
)

Insert @Country Values
(1,'India',1),
(2,'England',1),
(3,'Ireland',1)

Insert @State Values
(1,'Delhi',1,2),
(2,'Maharashtra',1,2)

Insert @City Values
(1,'Pune',2,3),
(2,'Mumbai',2,3)

DEclare @Search Varchar(30) = 'Ind'
Select Distinct Case 
When C.CountrName Like '%'+@Search+'%' Then C.CountrName
When S.StateName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')
When Ci.CityName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+' - '+IsNull(Ci.CityName,'')
End As SearchValue
FRom @Country C
Left Join @State S On S.CountryId = C.CountryId
Left Join @City Ci On Ci.StateId = S.StateId
Where 1 = Case
 When C.CountrName Like '%'+@Search+'%' Then 1
 When S.StateName Like '%'+@Search+'%' Then 1
 When Ci.CityName Like '%'+@Search+'%' Then 1
End

Thanks

Santhosh

---

Please have look on the comment

Answer 2

Hi Santhosh,

Thanks a lot.

Your code returns the expected output,except for the level.

Eg. 

India- Maharashtra-Mumbai (City)

What changes do I make to show the level?

Ameya

Answer 3

plz try this

create table #country(Country_ID int,Country_Name varchar(50), level_id int )
insert into #country values(1,'India',1),(2,'England',1),(3,'Ireland',1)

create table #State(State_ID int,State_Name varchar(50), Country_ID int,level_id int )
insert into #State values(1,'Delhi',1,2),(2,'Maharashtra',1,2)

create table #City(City_ID int,City_Name varchar(50), State_ID int,level_id int )

insert into #City values(1,'Pune',2,3),(2,'Mumbai',2,3)

declare @Str as varchar(50)='I'
declare @SelectQuery as varchar(8000)
set @SelectQuery='select Country_Name+''(Country)'' Output from #country where Country_Name like ''%'+@Str+'%'''
set @SelectQuery=@SelectQuery +' union select c.Country_Name+''-''+s.State_Name+''(State)'' Output from #State s inner join #country c on (s.Country_ID=c.Country_ID) where s.State_Name like ''%'+@Str+'%'''
set @SelectQuery=@SelectQuery +' union select c.Country_Name+''-''+s.State_Name+''-''+t.City_Name+''(City)'' Output from #City t inner join #State s on (t.State_ID=s.State_ID) inner join #country c on (s.Country_ID=c.Country_ID) where t.City_Name like ''%'+@Str+'%'''

print @SelectQuery
exec(@SelectQuery)

Answer 4

Hi Johnson,

Thanks!!

How can I make level part(city,state,country..) of the output dynamic based the level_ID?

Ameya

Answer 5

U mean to say u want level Id to be displayed.Please expalin

---

Please have look on the comment

Answer 6

Check this

Declare @Country Table
(
CountryId Int,
CountrName Varchar(30),
LevelId Int
)
Declare @State Table
(
StateID Int,
StateName Varchar(30),
CountryId Int,
LevelId Int
)
Declare @City Table
(
CityId Int,
CityName Varchar(30),
StateId Int,
LevelId Int
)

Insert @Country Values
(1,'India',1),
(2,'England',1),
(3,'Ireland',1)

Insert @State Values
(1,'Delhi',1,2),
(2,'Maharashtra',1,2)

Insert @City Values
(1,'Pune',2,3),
(2,'Mumbai',2,3)

Declare @Search Varchar(30) = 'Ind'
Select Distinct Case 
When C.CountrName Like '%'+@Search+'%' Then C.CountrName +'(Country)'
When S.StateName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+'(State)'
When Ci.CityName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+' - '+IsNull(Ci.CityName,'')+'(City)'
End As SearchValue
FRom @Country C
Left Join @State S On S.CountryId = C.CountryId
Left Join @City Ci On Ci.StateId = S.StateId
Where 1 = Case
 When C.CountrName Like '%'+@Search+'%' Then 1
 When S.StateName Like '%'+@Search+'%' Then 1
 When Ci.CityName Like '%'+@Search+'%' Then 1
End

---

Please have look on the comment

Answer 7

Each table row has a level_ID which refers to a entry in the level table.I need level_name to be displayed at the end of the output string.

Eg. in the above e.g.

search string - 'land' return two outputs England and Ireland,both have the level_ID 1,which refers to country in the Level Table.So the output should be

England (Country)

Ireland(Country)

search string - 'mum'  returns Mumbai.Since it is a city (level_ID=3 in the Level Table) the output should be 

India- Maharashtra-Mumbai(City)

I don't want to keep it static as Johnson has suggested above.I want it to be fetched dynamically based on the level ID

Answer 8

0

Check this

Declare @Country Table
(
CountryId Int,
CountrName Varchar(30),
LevelId Int
)
Declare @State Table
(
StateID Int,
StateName Varchar(30),
CountryId Int,
LevelId Int
)
Declare @City Table
(
CityId Int,
CityName Varchar(30),
StateId Int,
LevelId Int
)

Insert @Country Values
(1,'India',1),
(2,'England',1),
(3,'Ireland',1)

Insert @State Values
(1,'Delhi',1,2),
(2,'Maharashtra',1,2)

Insert @City Values
(1,'Pune',2,3),
(2,'Mumbai',2,3)

Declare @Search Varchar(30) = 'Ind'
Select Distinct Case 
When C.CountrName Like '%'+@Search+'%' Then C.CountrName +'(Country)'
When S.StateName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+'(State)'
When Ci.CityName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+' - '+IsNull(Ci.CityName,'')+'(City)'
End As SearchValue
FRom @Country C
Left Join @State S On S.CountryId = C.CountryId
Left Join @City Ci On Ci.StateId = S.StateId
Where 1 = Case
 When C.CountrName Like '%'+@Search+'%' Then 1
 When S.StateName Like '%'+@Search+'%' Then 1
 When Ci.CityName Like '%'+@Search+'%' Then 1
End

---

Please have look on the comment

Answer 9

Hi,

Please try the below

create table C1 (Country_ID int,Country_Name varchar(10),Level_ID int)
insert into C1 select 1,'India',1
insert into C1 select 2,'England',1
insert into C1 select 3,'Ireland',1
create table S1 (state_ID int,State_Name varchar(20),Country_ID int,Level_ID int)
insert into S1 select 1,'Delhi',1,2
insert into S1 select 2,'Maharashtra',1,2
create table Ci1 (City_ID int,City_Name varchar(10),State_ID int,Level_ID int)
insert into Ci1 select 1,'Pune',2,3
insert into Ci1 select 2,'Mumbai',2,3
Declare @name varchar(30)='land'
select distinct case
when Country_Name like '%'+@name+'%' Then Country_Name+'(country)'
when State_Name like '%'+@name+'%' Then Country_Name+'-'+State_Name+'(State)'
when City_Name like '%'+@name+'%' Then Country_Name+'-'+Isnull(State_Name,'')+'-'+ISNULL(City_Name,'')+'(City)'
end from C1
left join S1
on C1.Country_ID=S1.Country_ID
left join Ci1
on S1.state_ID=Ci1.State_ID
where C1.Country_Name like '%'+@name+'%' or S1.State_Name like '%'+@name+'%' or Ci1.City_Name like '%'+@name+'%'

Answer 10

I want the level part to be fetched dynamically based on the level_id of the matched row,not static.May be one more join.Is it possible?

Answer 11

Hi,

Did u check with new query i have pasted, it fetches even the level based on search criteria.

---

Please have look on the comment

Answer 12

From the table, we can understand that the level value for a particular table is always constant. in a table it will not come 1 and 2. Only 1 will be there. Am I correct ?.

Then you can use level part static, just like we are using table name static

Answer 13

Hi Santhosh,

When C.CountrName Like '%'+@Search+'%' Then C.CountrName +'(Country)'
When S.StateName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+'(State)'
When Ci.CityName Like '%'+@Search+'%' Then C.CountrName+' - '+Isnull(S.StateName,'')+' - '+IsNull(Ci.CityName,'')+'(City)'

In the above code the Country,Sate and city values are hard-coded,I want them to be dynamic based on the level where the match is found.

In future i may want to add new values in the levels table like county,councils,etc.

.For for country like England my state table may hold county values with different level_ID.

Answer 14

Hi,

please keep another table which stores level id and and it's  name. then we can join with that table also in my query to get level value dynamic.

From the table, we can understand that the level value for a particular table is always constant. in a table it will not come 1 and 2. Only 1 will be there. Am I correct ?.

Then you can use level part static, just like we are using table name static

Answer 15

Hi Johnson,

I do i have separate table to store level values,not sure where to place the Join clause.

As i have said in the above reply,state and city tables may have different level values based on the country.

e.g.

For for country like England my state table may hold county values with different level_ID.

Answer 16

I have added #level table

create table #country(Country_ID int,Country_Name varchar(50), level_id int )
insert into #country values(1,'India',1),(2,'England',1),(3,'Ireland',1)

create table #State(State_ID int,State_Name varchar(50), Country_ID int,level_id int )
insert into #State values(1,'Delhi',1,2),(2,'Maharashtra',1,2)

create table #City(City_ID int,City_Name varchar(50), State_ID int,level_id int )

insert into #City values(1,'Pune',2,3),(2,'Mumbai',2,3)

create table #Level(Level_ID int,Level_Name varchar(50) )
insert into #Level values(1,'country'),(2,'State'),(3,'City')


declare @Str as varchar(50)='I'
declare @SelectQuery as varchar(8000)
set @SelectQuery='select c.Country_Name+''('' + l.Level_Name + '')'' Output from #country c inner join #level l on(c.level_id=l.level_id) where c.Country_Name like ''%'+@Str+'%'''
set @SelectQuery=@SelectQuery +' union select c.Country_Name+''-''+s.State_Name+''('' + l.Level_Name + '')'' Output from #State s inner join #country c on (s.Country_ID=c.Country_ID) inner join #level l on(s.level_id=l.level_id) where s.State_Name like ''%'+@Str+'%'''
set @SelectQuery=@SelectQuery +' union select c.Country_Name+''-''+s.State_Name+''-''+t.City_Name+''('' + l.Level_Name + '')'' Output from #City t inner join #State s on (t.State_ID=s.State_ID) inner join #country c on (s.Country_ID=c.Country_ID) inner join #level l on(t.level_id=l.level_id) where t.City_Name like ''%'+@Str+'%'''

print @SelectQuery
exec(@SelectQuery)

Answer 17

Thanks a lot Johnson and Santhosh

Answer 18

Please mark it answer if its answers your requirement.

---

Please have look on the comment