1 query on 3 tables?

[Ondernemer]

Hi all,

I have been reading my books about this, but can't seem to find the
solution.

Example:

Picture 3 tables:

TABLE_1
-------------
item_id INT
subject VARCHAR
body TEXT
------------

TABLE_2
-------------
item_id INT
subject VARCHAR
body TEXT
------------

TABLE_1
-------------
item_id INT
subject VARCHAR
body TEXT
------------

All 3 tables have a FULLTEXT index on SUBJECT.

I want to create 1 query that searches ALL 3 tables with a MATCH (subject)
AGAINST ('$keywords' IN BOOLEAN MODE) and have the results neatly underneath
eachother as if I only searched 1 table.

Is this even possible? If not, what would be the workaround?

Thanks.

[Ondernemer]

oops, last table_1 in the example should read : TABLE_3

[Rich R]

"Ondernemer" <no**@email.com> wrote in message
news:10*************@corp.supernews.com...

Hi all,

I have been reading my books about this, but can't seem to find the
solution.

Example:

Picture 3 tables:

TABLE_1
-------------
item_id INT
subject VARCHAR
body TEXT
------------

TABLE_2
-------------
item_id INT
subject VARCHAR
body TEXT
------------

TABLE_1
-------------
item_id INT
subject VARCHAR
body TEXT
------------

All 3 tables have a FULLTEXT index on SUBJECT.

I want to create 1 query that searches ALL 3 tables with a MATCH (subject)
AGAINST ('$keywords' IN BOOLEAN MODE) and have the results neatly underneath eachother as if I only searched 1 table.

Is this even possible? If not, what would be the workaround?

Thanks.

How about this?

CREATE TABLE Table1(
item_id INT,
subject VARCHAR(35),
body TEXT,
FULLTEXT(subject));

CREATE TABLE Table2(
item_id INT,
subject VARCHAR(35),
body TEXT,
FULLTEXT(subject));

CREATE TABLE Table3(
item_id INT,
subject VARCHAR(35),
body TEXT,
FULLTEXT(subject));

insert into table1 values(1,'Subject1','Some text'),(2,'Subject2','Some more
text');
insert into table2 values(15,'Subject1','Some
text'),(2,'Subject4','ABABABA');
insert into table3 values(11,'Subject1','Some text');

SELECT item_id,subject,body FROM Table1 WHERE MATCH(Subject)
AGAINST('Subject1' IN BOOLEAN MODE)
UNION SELECT item_id,subject,body FROM Table2 WHERE MATCH(Subject)
AGAINST('Subject1' IN BOOLEAN MODE)
UNION SELECT item_id,subject,body FROM Table3 WHERE MATCH(Subject)
AGAINST('Subject1' IN BOOLEAN MODE);

You can probably accomplish the same thing using MERGED tables though I
haven't worked with merged tables.

Regards,
Rich

[Graham Drabble]

On 23 Dec 2004 "Ondernemer" <no**@email.com> wrote in
news:10*************@corp.supernews.com:

<snip>

All 3 tables have a FULLTEXT index on SUBJECT.

I want to create 1 query that searches ALL 3 tables with a MATCH
(subject) AGAINST ('$keywords' IN BOOLEAN MODE) and have the
results neatly underneath eachother as if I only searched 1 table.

Is this even possible? If not, what would be the workaround?

Look up UNION queries. Basically they let you run multiple queries
and then merge the output.

The syntax is

SELECT ...
UNION [ALL | DISTINCT]
SELECT ...
[UNION [ALL | DISTINCT]
[...]

So you would just need to write your 3 SELECT queries and then join
them by UNION ALL.

--
Graham Drabble
If you're interested in what goes on in other groups or want to find
an interesting group to read then check news.groups.reviews for what
others have to say or contribute a review for others to read.