Merging two tables in mysql query !

[coool]

Hi :)

let's say I have two database tables

FruitsTable
Id..........name..........color
13..........a..................green
25..........k..................yellow
32..........o..................red

VegetablesTable
Id..........name..........color
13..........d..................green
25..........g..................white
32...........l..................red

now can I do that ?


<?php

$fields="Id,name";
$tables="FruitsTable,VegetablesTabes";
$conditions="color in 'green,red'";

$sql="SELECT $fields FROM $tables WHERE $conditions";
?>


error: Id is ambiguous !! :eek:

[bubbisthedog]

All you have to do is prefix 'color' with a table qualifier:

$conditions="FruitsTable.color in 'green,red'";

[coool]

What do you think of this way to get the result ?


$query = "SELECT $fields FROM FruitsTable WHERE $conditions UNION SELECT $fields FROM VegetablesTable WHERE $conditions";

[bubbisthedog]

I do not understand why you're doing that. Did my suggestion not work? If not, then how did it not work?

[coool]

I'm using many tables.. and user is chosing fields and tables

anyway i've solved the problem already by using UNION and looping for each table :)

thanks