Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    Offtone.com - In the works...

  • #2
    Supreme Overlord Spookster's Avatar
    Join Date
    May 2002
    Location
    Marion, IA USA
    Posts
    6,273
    Thanks
    4
    Thanked 83 Times in 82 Posts
    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%' ";
    Spookster
    CodingForums Supreme Overlord
    All Hail Spookster

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Offtone.com - In the works...

  • #4
    New Coder
    Join Date
    Jul 2002
    Posts
    87
    Thanks
    0
    Thanked 0 Times in 0 Posts
    $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'";

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Location
    Ontario, Canada
    Posts
    183
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Pretty sure that && isn't a MySQL operator

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

    Thanks for the replies.
    Offtone.com - In the works...


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •