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)

    help building a JOIN, possibly?

    Hey,

    I'm going to be implementing an auto complete so what I'm doing is grabbing all the IDs from one table and checking a 2nd table for the ID so that I can grab the persons name.

    What I'm having issue with is how I should do this. Meaning, should I use a JOIN or two SELECTs. I would like to use a JOIN if possible.

    table: vip
    columns
    --------
    v_id - vid1(FK) - vid2(FK) - vconfirmed(enum[y, n])

    table: customers
    columns
    ------------------
    c_id - fname - lname

    c_id is the primary key which is used in the vip table as foreign keys.

    The reason I need both tables is because if they change their names I want it to update dynamically. Otherwise I would just store the names in the vip table.

    So what I'm doing is as follow:

    Code MySQL:
    SELECT vid2
    FROM vip
    WHERE vconfirmed = "Y"
    AND vid1 = 70

    But for me to have the names I would need the 2nd table. So something like this is what I'm looking for:


    Code MySQL:
    SELECT vip.vid2, customers.id, customers.fname, customers.lname
    FROM vip
    INNER JOIN customers
    ON vip.vid2 = customers.id
    WHERE vip.vconfirmed = "Y"
    AND vip.vid2 = 70

    If I'm user 70, I want to grab all the IDs that match me. Take those IDs and join them with customers table that match vid2.

    I'm not sure if I'm doing it correctly? Can someone lend a hand? Thanks

  2. #2
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry forgot to add the rest.

    I would also need to select only the names that are matched (or close to it)

    Code MySQL:
    SELECT vip.vid2, customers.id, customers.fname, customers.lname
    FROM vip
    INNER JOIN customers
    ON vip.vid2 = customers.id
    WHERE vip.vconfirmed = "Y"
    AND customer.fname LIKE "a%"
    AND vip.vid2 = 70

    Assuming "a" was the letter that was asked for.

  3. #3
    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)
    what happened when you tested it?

    i think you mixed up your vip1 and vip2 columns

    in the first example, you supply a value for vip1 and then pull vip2, but in subsequent examples, you supply a value for vip2 and then pull vip2

    if you fix that, i think your query should work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh yes, you are correct.

    Thanks

  5. #5
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey,

    The query works perfect.

    What I'm trying to do now is flip the query around. Basically what's happening right now is that if VIP user1 requests VIP user2, then user1 can speak to user2 but user2 cannot speak to user1

    Code MySQL:
    SELECT vip.userid2, customers.id, customers.name, customers.lname 
    FROM friends 
    INNER JOIN customers 
    ON vip.userid2 = customers.id 
    WHERE vip.vconfirmed = "Y" 
    AND vip.userid1 = 70

    So user1 -> user2

    but not

    user1 <- user2

  6. #6
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never mind I got it. I did a UNION and it worked perfectly.


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
  •