How to write this as join

Discussion in 'MySQL' started by kevbo22, Jul 17, 2013.

0
  1. #1
    ("select * from c_projects where projects_id=$id AND active=1 AND item in (select item from cl_purchase where cemail='$_SESSION[member_username]' AND active > 0 AND item in permissions =$id)")

    Thanks for your help!
     
    Solved! View solution.
    Last edited: Jul 17, 2013
    kevbo22, Jul 17, 2013 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #2
    how about

    $sql = "select *
from c_projects
join cl_purchase on c_projects.item = cl_purchase.item
where cl_purchase.cemail = '{$_SESSION[member_username]}'
and cl_purchase.active > 0";
    PHP:
    or 

    $sql = "select *
from c_projects
join cl_purchase on c_projects.item = cl_purchase.item and cl_purchase.active > 0
where cl_purchase.cemail = '{$_SESSION[member_username]}'";
    PHP:
     
    sarahk, Jul 17, 2013 IP
    ryan_uk likes this.
  3. kevbo22

    kevbo22 Active Member

    Messages:
    149
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #3
    Well this query works for the first two tables:
    ("select * from c_projects where projects_id=$id AND active=1 AND item in (select item from c_purchase where cemail='$_SESSION[member_username]' AND active > 0)");

    But I need to add in the third table
    AND item in permissions = $id
    and I need to write this up as a join for all three tables
    Thanks for the help
     
    kevbo22, Jul 17, 2013 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    28,830
    Likes Received:
    4,541
    Best Answers:
    123
    Trophy Points:
    665
    #4
    what does "AND item in permissions = $id" even mean?

    the table is called permissions? the column in permissions is called what?

    you should be able to follow the syntax from what I've given to add the new join though
     
    sarahk, Jul 17, 2013 IP
  5. kevbo22

    kevbo22 Active Member

    Messages:
    149
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    53
    #5
    what does "AND item in permissions = $id" even mean?
    it means the item in permissions would also be equal to the variable $id

    the table is called permissions? the column in permissions is called what?
    Yes the table is permissions and the column in permissions is item

    I dont know joins at all, they are just making me go crazy.
     
    kevbo22, Jul 17, 2013 IP
  6. #6 
    $sql="select *
from c_projects
join cl_purchase on c_projects.item = cl_purchase.item
join permissions on c_projects.item = permissions.item
where cl_purchase.cemail = '{$_SESSION[member_username]}'
and cl_purchase.active > 0
and permissions.item = '{$id}'";
    PHP:
     
    sarahk, Jul 17, 2013 IP