www.webdeveloper.com
Results 1 to 2 of 2

Thread: Multiple table query

Hybrid View

  1. #1
    Join Date
    Jun 2009
    Posts
    1

    Question Multiple table query

    alright, I've been banging my head against this query for the past couple of days. Here's the deal, I hope I don't confuse you too much trying to explain this. I have 3 tables(listed below). Table 1 has general site login crap, table2 has application specific info(there will be more of these later), and table3 has information that has been used already.

    Code:
    table1:
     email
     password
     enddate
    
    table2:
     email
     catagory
     code
    
    table3
     email
     code
    Every email in table2 will be in table1, but not neccessarily the other way around. Table3 is organized so after a user selects an item, that item is populated in table3 with the user's email and the code of the product selected.

    Example:
    joe@blah.com is signed up for the app, so he has an entry there. He has already selected several codes, so he has a few entries in table3. The tables might look something like this now:
    Code:
    Table1:
    joe@blah.com mypassword 06-04-2010
    sally@blah.com mypassword 06-04-2010
    greg@blah.com mypassword 06-04-2010
    bill@blah.com mypassword 06-04-2010
    harry@blah.com mypassword 06-04-2010
    donnie@blah.com mypassword 06-04-2010
    george@blah.com mypassword 06-04-2010
    
    Table2:
    joe@blah.com cat1 111111
    sally@blah.com cat1 222222
    bill@blah.com cat1 333333
    harry@blah.com cat1 444444
    greg@blah.com cat2 555555
    george@blah.com cat1 666666
    
    Table3:
    joe@blah.com 222222
    joe@blah.com 444444
    sally@blah.com 111111
    sally@blah.com 444444
    Using this, I'm hoping to display table2 items who's catagory matches the user's AND who's code is not listed in table3 next to the user's email. So, if joe were logged in, it would display items:
    bill@blah.com cat1 333333
    george@blah.com cat1 666666


    This is what I've got right now. I know the third statement( $query .= "AND '$email'!=`$table2`.email "; ) is one of the problems. I'm not even sure if it is possible with this setup, so if need be, I'd even be willing to reorganize the tables...just keep in mind that down the road other apps will be added, and they will get their own table2 and table3.

    Code:
     ...run code to get logged in user's information and set to $email, $category, and $code...
    
     $query = "SELECT * FROM table1, table2, table3 ":
     $query .= "WHERE '$category'=`$table2`.faction ";
     $query .= "AND '$category'=`$table2`.faction ";
     $query .= "AND '$code'!=`$table2`.code ";
     $query .= "AND '$email'!=`$table2`.email ";
    Any ideas? I'm a bit of a mysql noob...but I google well in most cases!

  2. #2
    Join Date
    Jun 2009
    Location
    Canada/Australia
    Posts
    3
    It looks like you need the "EXISTS" keyword or the "IN" keyword.

    I see no reason for including table1 in the sql, so I think this is roughly what you need
    e.g. (In straight SQL)

    Select *
    FROM table2 t2
    WHERE not exists (select 1 from table3 where email = t2.email)
    and @category = t2.faction
    and @code = t2.code
    and @email = t2.email

    or

    Select *
    FROM table2 t2
    WHERE not t2.email not in (select email from table3)
    and @category = t2.faction
    and @code = t2.code
    and @email = t2.email

    p.s. It's bad practice to use ("Select *"), you should specify the individual field names in case table structures change in the future.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles