none
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?

    Monday, November 19, 2012 4:57 AM

Answers

  • You cannot vote on your own post
    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

    • Marked as answer by Amey0072 Monday, November 19, 2012 9:52 AM
    Monday, November 19, 2012 6:42 AM
  • 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)

    • Marked as answer by Amey0072 Monday, November 19, 2012 8:06 AM
    Monday, November 19, 2012 7:51 AM

All replies

  • 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

    Monday, November 19, 2012 5:33 AM
  • 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

    Monday, November 19, 2012 5:53 AM
  • 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)

    Monday, November 19, 2012 5:56 AM
  • Hi Johnson,

    Thanks!!

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

    Ameya

    Monday, November 19, 2012 6:09 AM
  • U mean to say u want level Id to be displayed.Please expalin

    Please have look on the comment

    Monday, November 19, 2012 6:12 AM
  • 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

    Monday, November 19, 2012 6:15 AM
  • 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



    • Edited by Amey0072 Monday, November 19, 2012 6:23 AM
    Monday, November 19, 2012 6:20 AM
  • You cannot vote on your own post
    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

    • Marked as answer by Amey0072 Monday, November 19, 2012 9:52 AM
    Monday, November 19, 2012 6:42 AM
  • 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+'%'

    Monday, November 19, 2012 6:44 AM
  • 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?
    Monday, November 19, 2012 6:45 AM
  • 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

    Monday, November 19, 2012 6:49 AM
  • 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

    Monday, November 19, 2012 6:56 AM
  • 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.



    • Edited by Amey0072 Monday, November 19, 2012 7:01 AM
    Monday, November 19, 2012 7:00 AM
  • 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

    Monday, November 19, 2012 7:05 AM
  • 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.


    • Edited by Amey0072 Monday, November 19, 2012 7:13 AM
    Monday, November 19, 2012 7:12 AM
  • 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)

    • Marked as answer by Amey0072 Monday, November 19, 2012 8:06 AM
    Monday, November 19, 2012 7:51 AM
  • Thanks a lot Johnson and Santhosh
    Monday, November 19, 2012 8:07 AM
  • Please mark it answer if its answers your requirement.

    Please have look on the comment

    Monday, November 19, 2012 9:31 AM