April 5, 2012 at 2:44 am
Hi, I have a database (for real estate) with say 5 tables (property, state, location, area and agents). I am trying to run a search on the database based on a client inputing 'random' text string (from a mobile phone by sms) to search for availabe properties e.g "2 bedroom house in erith" and these should somehow search some pre-specified tables for 'any' or 'all' occurence of the individual words in the search text string and bring back any result from joining the tables (since they are normalised). I hope this makes sense. Thank you. 🙂
April 5, 2012 at 3:06 am
Hi
Hard to help without some more information - could you post some DDL's (table create statements) sample data and expected results please.
Andy
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 3:39 am
Hi Andy, thanks for you quick response. Here you go;
CREATE TABLE [dbo].[rhc_Properties](
[PropertyID] [int] IDENTITY(1,1) NOT NULL,
[AreaID] [int] NULL,
[StateID] [int] NULL,
[TypeID] [int] NULL,
[LocationID] [int] NULL,
[TransactionTypeName] [nvarchar](10) NULL,
[Price] [nchar](20) NULL,
[AgentID] [int] NULL,
[Description] [varchar](500) NULL,
[NoOfRooms] [nchar](10) NULL,
[TitleID] [int] NULL,
[AddedDate] [datetime] NULL,
[AddedBy] [nvarchar](256) NULL,
[ExpireDate] [datetime] NULL,
CONSTRAINT [PK_rhc_castlesProperty] PRIMARY KEY CLUSTERED
(
[PropertyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[rhc_Agents](
[AgentID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](256) NULL,
[Address1] [nvarchar](100) NULL,
[Address2] [nvarchar](100) NULL,
[Address3] [nvarchar](100) NULL,
[POBox] [nvarchar](20) NULL,
[StateID] [int] NULL,
[Telephone1] [nvarchar](20) NULL,
[Telephone2] [nvarchar](20) NULL,
[Fax] [nvarchar](20) NULL,
[Mobile1] [nvarchar](20) NULL,
[Mobile2] [nvarchar](20) NULL,
[Mobile3] [nvarchar](20) NULL,
[Mobile4] [nvarchar](20) NULL,
[Email] [nvarchar](100) NULL,
[OpeningHours] [nvarchar](25) NULL,
[ContactName] [nvarchar](256) NULL,
[Website] [nvarchar](50) NULL,
[Services] [nvarchar](100) NULL,
[AddedDate] [datetime] NULL,
[AddedBy] [nvarchar](256) NULL,
[LastUpdate] [datetime] NULL,
[UpdatedBy] [nvarchar](256) NULL,
CONSTRAINT [PK_rhc_Agents] PRIMARY KEY CLUSTERED
(
[AgentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[rhc_States](
[Name] [nvarchar](50) NULL,
[AddedBy] [nvarchar](256) NULL,
[StateID] [int] IDENTITY(1,1) NOT NULL,
CONSTRAINT [PK_rhc_States_1__55] PRIMARY KEY NONCLUSTERED
(
[StateID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[rhc_Locations](
[LocationID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[AreaID] [int] NULL,
[AddedBy] [nvarchar](256) NULL,
[AddedDate] [datetime] NULL,
CONSTRAINT [PK_rhc_Locations] PRIMARY KEY CLUSTERED
(
[LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[rhc_Areas](
[AreaID] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[StateID] [int] NULL,
[AddedBy] [nvarchar](256) NULL,
[AddedDate] [datetime] NULL,
CONSTRAINT [PK_rhc_Areas] PRIMARY KEY CLUSTERED
(
[AreaID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The result would include the Agent.Name, Agent.Mobile1, Properties.Description and Properties.Price and location.Name.
April 5, 2012 at 3:56 am
No probs
You just need to script some insert statements to populate the tables and post those on the thread 🙂
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 3:58 am
Ok Andy. Edited the post and extracted to an excel doc. Thanks 🙂
April 5, 2012 at 4:07 am
No, INSERT statements. Many people won't bother to open a spreadsheet (may contain viruses), and of those that do, most won't have the time or inclination to import all those sheets into your tables.
What I was going to say, though, is have you considered full-text indexing? It's not something I've used a lot myself, but I think this is the kind of thing it's designed for.
John
April 5, 2012 at 4:14 am
Hi Andy, thanks for the advise. That just goes to show my newbie level!!!
Never used full text search but I'll look into it now and in the mean time I have included the insert statements. Thank you.
INSERT INTO [dbo].[rhc_States]
([Name]
,[AddedBy])
VALUES
(<Name, nvarchar(50),>
,<AddedBy, nvarchar(256),>)
GO
INSERT INTO [dbo].[rhc_Properties]
([AreaID]
,[StateID]
,[TypeID]
,[LocationID]
,[TransactionTypeName]
,[Price]
,[AgentID]
,[Description]
,[NoOfRooms]
,[TitleID]
,[AddedDate]
,[AddedBy]
,[ExpireDate])
VALUES
(<AreaID, int,>
,<StateID, int,>
,<TypeID, int,>
,<LocationID, int,>
,<TransactionTypeName, nvarchar(10),>
,<Price, nchar(20),>
,<AgentID, int,>
,<Description, varchar(500),>
,<NoOfRooms, nchar(10),>
,<TitleID, int,>
,<AddedDate, datetime,>
,<AddedBy, nvarchar(256),>
,<ExpireDate, datetime,>)
GO
INSERT INTO [dbo].[rhc_Areas]
([Name]
,[StateID]
,[AddedBy]
,[AddedDate])
VALUES
(<Name, nvarchar(50),>
,<StateID, int,>
,<AddedBy, nvarchar(256),>
,<AddedDate, datetime,>)
GO
INSERT INTO [dbo].[rhc_Locations]
([Name]
,[AreaID]
,[AddedBy]
,[AddedDate])
VALUES
(<Name, nvarchar(50),>
,<AreaID, int,>
,<AddedBy, nvarchar(256),>
,<AddedDate, datetime,>)
GO
INSERT INTO [dbo].[rhc_Agents]
([Name]
,[Address1]
,[Address2]
,[Address3]
,[POBox]
,[StateID]
,[Telephone1]
,[Telephone2]
,[Fax]
,[Mobile1]
,[Mobile2]
,[Mobile3]
,[Mobile4]
,[Email]
,[OpeningHours]
,[ContactName]
,[Website]
,[Services]
,[AddedDate]
,[AddedBy]
,[LastUpdate]
,[UpdatedBy])
VALUES
(<Name, nvarchar(256),>
,<Address1, nvarchar(100),>
,<Address2, nvarchar(100),>
,<Address3, nvarchar(100),>
,<POBox, nvarchar(20),>
,<StateID, int,>
,<Telephone1, nvarchar(20),>
,<Telephone2, nvarchar(20),>
,<Fax, nvarchar(20),>
,<Mobile1, nvarchar(20),>
,<Mobile2, nvarchar(20),>
,<Mobile3, nvarchar(20),>
,<Mobile4, nvarchar(20),>
,<Email, nvarchar(100),>
,<OpeningHours, nvarchar(25),>
,<ContactName, nvarchar(256),>
,<Website, nvarchar(50),>
,<Services, nvarchar(100),>
,<AddedDate, datetime,>
,<AddedBy, nvarchar(256),>
,<LastUpdate, datetime,>
,<UpdatedBy, nvarchar(256),>)
GO
Thank you.
April 5, 2012 at 4:24 am
That's closer, but we need the actual values in the insert statements. Do you see why? Somebody can just run your CREATE TABLE statements, then run your INSERT statements, and they have a miniature replica of your environment right in front of them. This makes a lot easier for people to help you, and you will usually find you get the best and quickest answers in this way.
John
April 5, 2012 at 4:38 am
What I was going to say, though, is have you considered full-text indexing
Looking at what's needed so far this seems to be the way to go - again its not something I've used either
I may be seriously overly simplifying the problem but could you not use something like this?
SELECT
AGT.Name
,AGT.Mobile1
,P.Description
,P.Price
,L.Name
FROM
dbo.rhc_Properties AS P
LEFT JOIN dbo.rhc_Agents AS AGT
ON P.AgentID = AGT.AgentID
LEFT JOIN dbo.rhc_Areas AS A
ON P.AreaID = A.AreaID
LEFT JOIN dbo.rhc_Locations AS L
ON P.LocationID = L.LocationID
WHERE
p.description
LIKE '%Warehouse%'
OR p.description LIKE '%Plot%'
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 4:40 am
Ok, working on the inserts. THank you
April 5, 2012 at 4:48 am
Hi Andy, I tried this already but the users can search for say '1 bedroom at erith' and not just necessarily a word. Breaking the search down now, Erith might not necessarily be in the description but it exists in the location table (which can be found if the properties table is joined to the location table) and so on. Coming to think of it again, I might be over reaching here!!! :unsure:
April 5, 2012 at 5:41 am
There may be a million different better and more efficient ways of doing this however until one is suggested give this a shot (and a through test :-D)
DECLARE
@SearchString VARCHAR (8000)
SET
@SearchString = 'WAREHOUSE MOSHALASHI NEW IPAJA'
SELECT
AGT.Name
,AGT.Mobile1
,P.Description
,P.Price
,L.Name
FROM
dbo.rhc_Properties AS P
LEFT JOIN dbo.rhc_Agents AS AGT
ON P.AgentID = AGT.AgentID
LEFT JOIN dbo.rhc_Areas AS A
ON P.AreaID = A.AreaID
LEFT JOIN dbo.rhc_Locations AS L
ON P.LocationID = L.LocationID
INNER JOIN (SELECT
P2.PropertyID
,String.Item
FROM
dbo.rhc_Properties AS P2
CROSS APPLY dbo.DelimitedSplit8K(P2.Description, ' ') String
CROSS APPLY dbo.DelimitedSplit8K(@SearchString, ' ') SearchString
WHERE
String.Item = SearchString.Item
) AS SearchString ON P.PropertyID = SearchString.PropertyID
Andy
Edit: You'll also need Jeffs String splitter
http://www.sqlservercentral.com/articles/Tally+Table/72993/
==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe
April 5, 2012 at 5:51 am
A very big thank you Andy. I give it a go and get back to you. Many thanks:hehe:
April 5, 2012 at 6:20 am
John Mitchell-245523 (4/5/2012)
No, INSERT statements. Many people won't bother to open a spreadsheet (may contain viruses), and of those that do, most won't have the time or inclination to import all those sheets into your tables.What I was going to say, though, is have you considered full-text indexing? It's not something I've used a lot myself, but I think this is the kind of thing it's designed for.
John
Hi John, here are the insert scripts
insert into rhc_Areas values (' Lagos Island ', ' 25 ','westo','39767.799631169');
insert into rhc_Areas values (' Lagos Mainland ', ' 25 ','westo','39767.799631169');
insert into rhc_Locations values (' Ikoyi ', ' 1 ','westo','39767.7979640394');
insert into rhc_Locations values (' Dopemu ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Egbeda ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Iba ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Idimu ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Ifako Agege ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Iju ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Ipaja ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Ajegunle ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Apapa ', ' 2 ','westo','39767.7979640394');
insert into rhc_Locations values (' Victoria Island ', ' 1 ','westo','39767.7979640394');
insert into rhc_States values (' westo ', ' 1 ');
insert into rhc_States values (' westo ', ' 2 ');
insert into rhc_States values (' westo ', ' 25 ');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 14 ' , ' 8 ', ' Rent' , ' 700000 ',' 1499',' WAREHOUSE @ MOSHALASHI OFF NEW IPAJA RD ',' 0',' 5',' 40936.7958969907',' Jaleel',' 40950.7958969907');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 12 ' , ' 59 ', ' Rent' , ' 1000000 ',' 1499',' WAREHOUSE ON A TARRED RD @ OFF ISHERI ROUNDABOUT ',' 0',' 5',' 40936.7967304398',' Jaleel',' 40950.7967304398');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 14 ' , ' 63 ', ' Rent' , ' 1000000 ',' 1502',' WAREHOUSE ON A TARRED RD OFF AKOWONJO RD ',' 0',' 5',' 40936.7969877315',' Jaleel',' 40950.7969877315');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 7 ' , ' 48 ', ' Rent' , ' 180000 ',' 1502',' 2Nos 3B/R FLAT 2T.2B @ ABARANJE',' 0',' 5',' 40936.7973396644',' Jaleel',' 40950.7973396644');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 4 ' , ' 61 ', ' Rent' , ' 6500000 ',' 1499',' 3B/R BUNGALOW SETBACK @ IKOTUN ',' 0',' 5',' 40936.7976052431',' Jaleel',' 40950.7976052431');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 15 ' , ' 63 ', ' Rent' , ' 18000000 ',' 1504',' 4B/R DUPLEX @ OFF PIPELINE',' 0',' 5',' 40936.7978307523',' Jaleel',' 40950.7978307523');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 10 ' , ' 5 ', ' Sale' , ' 15000000 ',' 1504',' 2 PLOTS WITH 15 SHOPS, CORNERPIECE @ BY ST. FRANCIS',' 0',' 5',' 40936.7995583333',' Jaleel',' 40950.7995583333');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 10 ' , ' 44 ', ' Sale' , ' 5500000 ',' 1502',' A PLOT OF LAND @ OFF ADEMOYE ST, ILE-EPO',' 0',' 5',' 40936.7997522338',' Jaleel',' 40950.7997522338');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 10 ' , ' 44 ', ' Sale' , ' 12000000 ',' 1502',' 2 PLOTS OF LAND @ ADEMOYE ST, ILE-EPO',' 0',' 5',' 40936.7999571759',' Jaleel',' 40950.7999571759');
insert into rhc_Properties values (' 2 ', ' 25 ', ' 10 ' , ' 5 ', ' Sale' , ' 15000000 ',' 1504',' 2 PLOTS + UNCOMPLETED STRUCTURE APPROVED @ ILE-EPO',' 0',' 5',' 40936.8001653588',' Jaleel',' 40950.8001653588');
insert into rhc_Agents values (' SWEET HOME REAL ', ' Big Leaf House, 4th Floor (Rear Wing) 7, Oyin Jolayemi St, V.Island ', ' ' , ' ', ' ' , ' 25 ',' ',' ',' 01 2705805',' 8039322666',' 8022699091',' 8185643448',' ',' sweethome4real@yahoo.com',' ',' ',' ',' .Valuers .Estate Surveyors .Real Property Consultants .Real Estate .Estate Agents .Development Consu',' 40794.9397297106',' Jaleel',' NULL',' NULL');
insert into rhc_Agents values (' MISA LTD ', ' 12A Glover Rd, Ikoyi ', ' ' , ' ', ' ' , ' 25 ',' ',' ',' ',' 8025236801',' 8055058083',' 8052482162',' 8033000540',' ',' ',' ',' ',' .Valuers .Estate Surveyors .Real Property Consultants .Real Estate .Estate Agents .Development Consu',' 40794.9427493403',' Jaleel',' NULL',' NULL');
insert into rhc_Agents values (' MUTIU BALOGUN & CO ', ' Suite 01 1st Floor, NIM Building, Plot 22, Idowu Taylor St, P.M.B. 72400 V.Island ', ' ' , ' ', ' ' , ' 25 ',' 01-4068747',' 01-8111438',' ',' 01-4023091',' 8037128802',' 8071887376',' 7070253449',' murtrishbalog@yahoo.com, mb@murtrishbalogs.com',' ',' ',' http://www.murtrishbalogs.com',' .Valuers .Estate Surveyors .Real Property Consultants .Real Estate .Estate Agents .Development Consu',' 40794.9475568287',' Jaleel',' NULL',' NULL');
insert into rhc_Agents values (' BODE ADEDEJI PARTNERSHIP ', ' 15 ishop Oyewole St, V. Island ', ' ' , ' ', ' ' , ' 25 ',' 01-4616694',' 01-4613469',' ',' 01-4617577',' 8078649777',' 8037174098',' 7085202336',' bapestate@yahoo.com, bap_estate@yahoo.com, bapestate@bodeadedejipartnership.com',' ',' ',' http://www.bodeadedejipartnership.com',' .Valuers .Estate Surveyors .Real Property Consultants ',' 40794.9500584491',' Jaleel',' 40806.9807934375',' Jaleel');
insert into rhc_Agents values (' CHAPEL HILL PROPERTIES ', ' Suite 129, 1st Floor, Edo House, Plot 1225, Bishop Oluwole St, V.Island ', ' ' , ' ', ' ' , ' 25 ',' 01-7622487',' ',' ',' 8088677766',' 7025633562',' ',' ',' onebillionnaira@yahoo.com',' ',' ',' ',' .Valuers .Estate Surveyors .Real Property Consultants .Real Estate .Estate Agents ',' 40794.9538436343',' Jaleel',' NULL',' NULL');
Thank you
April 5, 2012 at 6:57 am
Those decimals at the ends of the rows don't convert into datetime values, I'm afraid. But seriously, I really think full-text indexing is the way to go on this. Why roll your own solution when someone has already built one for you?
John
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply