Multiple TBL with no id

[JakesSA]

How would you go about doing a search on two tables that are NOT linked through a comman id of some sort. Is there a way to write it into a sql query.

eg

SELECT col_1, col_A FROM tbl_1, tbl_A WHERE desc LIKE '%s%';

[bad76]

Hi,
you CAN write a select of this kind, but you get from it a tableA x tableB result set.

if we make this
SELECT col_1, col_A FROM tbl_1, tbl_A WHERE tbl_1.desc LIKE '%s%' OR tbl_A.desc LIKE '%s%'

we get for each valid value of col_1 ALL value of col_A and viceversa.

valid_col_1_1,col_A_1
valid_col_1_1,col_A_2
valid_col_1_1,col_A_3
valid_col_1_1,col_A_4
valid_col_1_1,col_A_5
....
valid_col_1_2,col_A_1
valid_col_1_2,col_A_2
valid_col_1_2,col_A_3
valid_col_1_2,col_A_4
valid_col_1_2,col_A_5
...
valid_col_A_1,col_1_1
valid_col_A_1,col_1_2
valid_col_A_1,col_1_3
valid_col_A_1,col_1_4
valid_col_A_1,col_1_5
....
valid_col_A_2,col_1_1
valid_col_A_2,col_1_2
valid_col_A_2,col_1_3
valid_col_A_2,col_1_4
valid_col_A_2,col_1_5
...

If the table haven't any join field you have to use 2 query.

see you

[JakesSA]

Please could you show me how to search three tables.

These tables arent linked in anyway, they are seperate from each other.

eg cars, van & boats

So if the user had to type in 'FORD' for example it will bring up the results in all three.

Thanks

[bad76]

hmm... that only
way i think is to make three select and show each one:

$res=mysql_query("select * from boat where name like 'FORD%'");
while($row=mysql_fetch_array($res)) echo $row[0];
$res=mysql_query("select * from car where name like 'FORD%'");
while($row=mysql_fetch_array($res)) echo $row[0];
$res=mysql_query("select * from van where name like 'FORD%'");
while($row=mysql_fetch_array($res)) echo $row[0];

Else another way is to rethink the db structure to refer at all cat, some like this:

db_vehicles
ID int(8) key unique
kind char(1) // C=car V=van B=boat
engine int(8)
length int(8)
num_of_wheel int // 0 for boat

and so on...
But i know that it's not always possible...

[JakesSA]

I found quite a nice way to join the tables

SELECT van AS name, cat_desc AS description
FROM van WHERE desc LIKE '%a%'
UNION
SELECT boat AS name, subcat_desc AS description
FROM boat WHERE desc LIKE '%a%'
UNION
SELECT car name, page AS description
FROM car WHERE desc LIKE '%a%'

this is joins it by column

[bad76]

Well done.
I'm working with mysql 3.x.x, so i don't know this.

see you