Complex SQL Query

Hi All
We have search page and want to query the database with search terms
entered.
The table details are like this
TapeRecords
ID (Primary Key)
ItemTitle
Location
Country
Keywords
TapeLogDetails
ID (primary key)
TapeNumber (Related to TapeRecords.ID)
Description
What I am looking for is the SQL Query to query the database for all the
records that match two search items i.e. "Burma" "Asia"
I want to search the combination of two in any of the following fields
TapeRecords.ItemTitle
TapeRecords.Location
TapeRecords.Country
TapeRecords.Keywords
TapeLogDetails.Decription
If the two keywords match AND combination of any of the above fields I want
to display the records.
For example "Burma" in decription and "Asia" in ItemTitle or "Burma" AND
"Asia" in Location
Is this possible?JP
Since you have not provided the DDL I have made some assumptions
CREATE TABLE w
(
COL1 VARCHAR(20)
)
INSERT INTO W VALUES ('URIDIMANT')
INSERT INTO W VALUES ('DIMANT')
INSERT INTO W VALUES ('URIRON')
INSERT INTO W VALUES ('RR')
CREATE TABLE w1
(
COL1 VARCHAR(20)
)
INSERT INTO W1 VALUES ('URIDIMANT')
INSERT INTO W1 VALUES ('DIMANTRON')
INSERT INTO W1 VALUES ('URI')
INSERT INTO W1 VALUES ('URIRON')
SELECT W.COL1,W1.COL1 FROM W JOIN W1
ON W.col1 LIKE '%' + W1.col1 + '%'
DROP TABLE W
DROP TABLE W1
"JP SIngh" <none@.none.com> wrote in message
news:urPcJYNIGHA.596@.TK2MSFTNGP10.phx.gbl...
> Hi All
> We have search page and want to query the database with search terms
> entered.
> The table details are like this
> TapeRecords
> ID (Primary Key)
> ItemTitle
> Location
> Country
> Keywords
>
> TapeLogDetails
> ID (primary key)
> TapeNumber (Related to TapeRecords.ID)
> Description
> What I am looking for is the SQL Query to query the database for all the
> records that match two search items i.e. "Burma" "Asia"
> I want to search the combination of two in any of the following fields
> TapeRecords.ItemTitle
> TapeRecords.Location
> TapeRecords.Country
> TapeRecords.Keywords
> TapeLogDetails.Decription
> If the two keywords match AND combination of any of the above fields I
> want to display the records.
> For example "Burma" in decription and "Asia" in ItemTitle or "Burma" AND
> "Asia" in Location
> Is this possible?
>
>
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. What you did post is a complete mess.
There is no magical, univeral "id', the rest of the names violate
ISO-11179 rules, are you really modeling tape records and not tapes
themselves? Why is there a detail table with a header table? etc.
Here is a guess.
CREATE TABLE Tapes
(tape_nbr INTEGER NOT NULL PRIMARY KEY,
tape_title VARCHAR(20) NOT NULL,
tape_loc VARCHAR(20) NOT NULL,
country_code CHAR(3) NOT NULL, --iso standard);
--normalize the data
CREATE TABLE TapeKeywords
(tape_nbr INTEGER NOT NULL
REFERENCES Tapes (tape_nbr)
ON UPDATE CASCADE,
ON DELETE CASCADE,
keyword VARCHAR(15) NOT NULL,
PRIMARY KEY (tape_nbr, keyword));
CREATE TABLE TapeLog
(tape_nbr INTEGER NOT NULL
REFERENCES Tapes (tape_nbr),
log_seq INTEGER NOT NULL,
PRIMARY KEY (tape_nbr, log_seq),
tape_description VARCHAR (100) NOT NULL);
Rows are not anything like records, and columns are not anything like
fields. This is foundations. This will mean that you have to clean up
your data before you can do anything. Now write two queries on Tapes
and TapeKeywords and UNION them. I would extract keywords from the
titles and use relational divisions on that single table myself.