SitePoint Sponsor

User Tag List

Results 1 to 14 of 14

Thread: Inner Join

  1. #1
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Australia
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Inner Join

    I'm a relative newcomer to PHP and I am having dramas with a select statement that I am trying to perform on a mysql database.

    $query = "SELECT account.*, client.client_Name, client.member_Type, client.active, client.user_Name, client.password, client.phone_Num, client.email
    FROM account INNER JOIN client ON account.mobile_Num = client.mobile_Num
    WHERE $metode LIKE '%$filter_mobile_Num%';";

    The WHERE statement works fine cause i've had it working in another statement. The inner join seems invalid and keeps returning an error.
    How do I select these fields from these 2 tables????

  2. #2
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your select for client.mobile_Num is missing. Can't do an inner join on something that you haven't selected.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  3. #3
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinyl-junkie
    Your select for client.mobile_Num is missing. Can't do an inner join on something that you haven't selected.
    Yes, you can.

  4. #4
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tazzy105
    The inner join seems invalid and keeps returning an error.
    The inner join seems fine to me (that is, if, like I posted on this same question a while ago), mobile_Num is principally the same data type and is NOT null in both tables...

    What is the error it is returning?

    Also, is there a reason to foriegn key off of the mobile number?

  5. #5
    SitePoint Addict pointbeing's Avatar
    Join Date
    Jun 2004
    Location
    London, UK
    Posts
    227
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tazzy105
    The inner join seems invalid and keeps returning an error.
    I suspect vinyl-junkie may be right here.

    Still, as always, maybe if you post the error message people can help better?

  6. #6
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, it would be a good idea if you provide the exact error you are getting, and also, you have an extra semicolon at the end of your query, before the double quotes.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by vinyl-junkie
    Can't do an inner join on something that you haven't selected.
    yes, you can

    the problem can only be $metode

    what does that resolve to?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    yes, you can

    the problem can only be $metode

    what does that resolve to?
    With a nickname like SQL Consultant, I almost hesitate to call your hand on this one, but you're wrong about being able to do an inner join on a variable you haven't first selected.

    I took some of my own code which has an inner join, just like the SQL we're debating here, and commented out the Select for one of the inner join variables. When I did that, I got an error - undefined variable.

    It's possible that $metode is also a problem, but the Select variable list is definitely a problem.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    go ahead, call my hand

    how many articles are in the database category?
    Code:
    select count(*)
      from categories
    inner
      join articles
        on categories.id
         = articles.category_id
     where categories.catname
         = 'database'
    who wrote the latest article?
    Code:
    select lastname
         , firstname
      from articles  
     where date_posted
         = ( select max(date_posted)
               from articles )
    i've got literally hundreds of examples, if you need more

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    go ahead, call my hand
    Hey, I guess I have to get a nickname before anyone listens to me...

    LOL

  11. #11
    SitePoint Addict silent's Avatar
    Join Date
    Jun 2004
    Location
    Roaming North America
    Posts
    220
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BTW, here is the original thread to which I referred in the above post:

    http://www.sitepoint.com/forums/showthread.php?t=184469

  12. #12
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    go ahead, call my hand

    <snip>

    i've got literally hundreds of examples, if you need more

    I will concede you are right, only if you can tell me why this query works only when I don't eliminate the select variable I've bolded. And more importantly, how to structure the query so it works fine without it.
    Code:
    SELECT 
    tblAlbums.Title, 
    tblAlbums.TitleSort, 
    tblAlbums.MediaType, 
    tblAlbums.AlbumID, 
    tblAlbums.DateAdded, 
    tblAlbums.Genre, 
    tblAlbums.UserField1, 
    tblArtists.The, 
    tblArtists.Artist, 
    tblArtists.ArtistID, 
    tblArtists.SortName, 
    tblAlbums.Notes, 
    tblAlbums.Total_Playing_Time, 
    tblAlbums.ArtistID 
    FROM tblArtists 
    INNER JOIN 
    tblAlbums ON tblArtists.ArtistID = tblAlbums.ArtistID 
    WHERE 
    userfield1='Bee Gees' 
    OR 
    tblArtists.Artist IN ('Bee Gees', 'Andy Gibb', 'Barry Gibb', 'Maurice Gibb', 'Robin Gibb') 
    OR 
    userfield1 LIKE '%Bee Gees%' 
    ORDER BY 
    tblArtists.SortName, tblAlbums.TitleSort, tblAlbums.MediaType
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    when you remove that bolded line, do you also remove the trailing comma on the preceding line?

    that's a great example of why i prefer leading commas instead of trailing commas

    here's your query, re-written:
    Code:
    SELECT tblAlbums.Title
         , tblAlbums.TitleSort
         , tblAlbums.MediaType
         , tblAlbums.AlbumID
         , tblAlbums.DateAdded
         , tblAlbums.Genre
         , tblAlbums.UserField1
         , tblArtists.The
         , tblArtists.Artist
         , tblArtists.ArtistID
         , tblArtists.SortName
         , tblAlbums.Notes
         , tblAlbums.Total_Playing_Time
         , tblAlbums.ArtistID 
      FROM tblArtists 
    INNER 
      JOIN tblAlbums 
        ON tblArtists.ArtistID 
         = tblAlbums.ArtistID 
     WHERE userfield1='Bee Gees' 
        OR tblArtists.Artist IN 
           ( 'Bee Gees', 'Andy Gibb', 'Barry Gibb'
           , 'Maurice Gibb', 'Robin Gibb' ) 
        OR userfield1 LIKE '%Bee Gees%' 
    ORDER 
        BY tblArtists.SortName
         , tblAlbums.TitleSort
         , tblAlbums.MediaType
    now it's easier to remove the bolded line, no?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Good eye there! I didn't realize that was the last variable in the Select list.

    And yes, after properly removing that variable this time, I do concede that you are right about it not being necessary.

    Um, here's your crown.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web


Bookmarks

Posting Permissions

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