Searching accross multiple tables with identical fields

Ok, I have the following tables:

Quotes, Avatars, Jokes, and Facts.

Each table has these fields for sure:

member, category, source, body, rating, times_rated, and table_id

Now this query doesn't work:

$sql = "SELECT member, category, source, body, rating, times_rated, table_id FROM quotes, avatars, jokes, facts WHERE member LIKE '%$member%' AND category LIKE '%$category%' AND source LIKE '%$source%' AND body LIKE '%$body%'";

The error:

Column: 'member' in field list is ambiguous.

How can I pull these fields from each table without having an incredibly long SQL query with a bunch of quotes.FIELDNAME and WHERE quotes.FIELDNAME LIKE ... AND avatars.FIELDNAME like...

Is this possible, or will I need to query the DB 4 times, once for each table?

Thanks a million for any help :)

If all tables have the same exact fields then the database needs to be redesigned and normalized. All those tables should be combined with an another field added to identify whether it is avatar, joke, etc. That would be the easiest in the long run.

However in the mean time to prevent ambiguity in field names between tables you need to explicitly state the table name for each field. Since you are doing a join across multiple tables with the same exact fields you will most likely want to do seperate queries for each table.

But to answer your question when you do a join between two or more tables that have identical field names you would do this:

$sql = "SELECT avatar.member, jokes.member ,FROM avatars, jokes WHERE avatar.member LIKE '%$member%' AND jokes.member LIKE '%$member%' ";

Not all my tables are the same, but they all have those fields. They each have their own fields for their own purpose.

About the joining: I was affraid of that. I think it'd be easier to simply loop through the table names in an array and perform 4 queries.

$sql = "SELECT member, category, source, body, rating, times_rated, table_id FROM quotes, avatars, jokes, facts WHERE member = '$member' && category = '$category' && source = '$source' && body = '$body'";

Pretty sure that && isn't a MySQL operator :confused:

I got it working though. I just mushed all for tables together into one like Spookster suggested :)

Thanks for the replies.