search

[shenshen]

a "bookstore" assignment, want a keyword search, there are 3 tables:
book, cd and tape, will search all the attributes in tables.

have inserted in book and cd table 1 record each, with the word desired, tape table is empty

use
select book., cd. from book, cd where book.title like '%$var%' or book.author like '%$var%' or cd.album_name like '%$var%' or cd.performer like '%$var%';

get 1 record (should be 1+1=2 records)
if use
select book., cd., tape.* from book, cd, tape where book.title like '%$var%' or book.author like '%$var%' or cd.album_name like '%$var%' or cd.performer like '%$var%' or tape.title like '%$var%' ;

get 0 record

what's wrong?

[barand]

When you use two tables like that without specifying a join condition you end up with (I think its called a cartesian join) where the fields from both tape. and book. form a new row in the resulting set, in all possible combinations. (Comes in useful for generating test data)

As you have only one record in each, your result has 1 row (1 * 1).

If you had 5 records in each you would have 25 rows in the result.

With 3 tables (second example) you finish up with A B C rows. In your case 1 1 0.

In your case you need either 3 queries or use UNION ensuring each part of the union selects the same number of fields.

Alternatively, place Tapes, Books and CDs in a single table with an identifier for each type (B,C or T)

HTH