SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    displaying my information PLUS friends info

    Hey,

    I'm trying to write a query where I can see my information plus anyone else who is my paypal friend.

    Code MySQL:
    SELECT wall.wall_message, wall.wall_date, customers.name, customers.lname
    FROM wall, friends
    INNER JOIN customers
      ON customers.id = wall.wall_toid
    WHERE wall.wall_toid = 70
    AND (friends.userid1 = 70 AND friends.userid2 = customers.id
         OR
         friends.userid1 = customers.id AND friends.userid2 = 70)

    I'm getting an error:
    #1054 - Unknown column 'friends.userid1' in 'where clause'

    But that column does exist.

    Also, I'm wondering if the AND condition will work properly? Basically I'm just grabbing anyone who matches me (id 70) on either userid1 or userid2 and whatever id is in either column.

  2. #2
    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)
    your FROM clause is a mix of old-style comma joins and JOIN syntax, and this often causes a syntax error

    try it like this --
    Code:
    SELECT wall.wall_message
         , wall.wall_date
         , customers.name
         , customers.lname
      FROM wall
    INNER 
      JOIN customers
        ON customers.id = wall.wall_toid
    INNER
      JOIN friends
        ON friends.userid1 = 70 AND friends.userid2 = customers.id
        OR friends.userid1 = customers.id AND friends.userid2 = 70
     WHERE wall.wall_toid = 70
    don't forget you haven't put any friends columns in the SELECT clause yet
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Aw nice but this works 99%.

    I removed the WHERE clause and it started showing me all my paypal friends, which works great. But I can't view my own posts?

    This is where I'm at now:
    Code MySQL:
    SELECT wall.wall_message
         , wall.wall_date
         , customers.name
         , customers.lname
      FROM wall
    INNER 
      JOIN customers
        ON customers.id = wall.wall_toid
    INNER
      JOIN friends
        ON friends.userid1 = 70 AND friends.userid2 = customers.id
        OR friends.userid1 = customers.id AND friends.userid2 = 70
    WHERE friends.friend = "Y"

    It seems I would have to INSERT myself into both columns of userid1 and userid2 in order for this to populate me. The problem is that it isnt a logical thing to do it that way.

  4. #4
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After the WHERE clause I added:

    OR customers.id = 70 and it showed my posts.... but it repeated it lots of times.

  5. #5
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did the following:

    Code MySQL:
    SELECT wall.wall_id, 
                     wall.wall_message, 
                     wall.wall_toid, 
                     wall.wall_fromid, 
                     wall.wall_date, 
                     customers.id, 
                     customers.name, 
                     customers.lname, 
                     customers.user_originalPic
                     FROM wall
                     INNER 
                      JOIN customers
                        ON customers.id = wall.wall_toid
                     INNER
                      JOIN friends
                        ON friends.userid1 = 70 AND friends.userid2 = customers.id
                        OR friends.userid1 = customers.id AND friends.userid2 = 70
                      INNER JOIN wall as wall_myself
                        ON wall_myself.wall_fromid = 70
                        AND wall_myself.wall_toid = 70
                     AND friends.friend = "Y"
                     ORDER BY wall_date DESC
                     LIMIT 0, 50

    But this duplicated the data without showing my own posts.

    thanks for the help so far

  6. #6
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fixed it. Did a UNION and did a WHERE clause to match my ID.

    thank you for the hand.


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
  •