SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to pull more data from a join

    Hi all,

    I'm trying to access a little more info that my current query is giving me.

    Here's the initial query:
    SELECT u.uid, u.name, u.mail, n.title AS 'group'
    FROM users u
    LEFT JOIN profile_values pv
    ON u.uid = pv.uid
    LEFT OUTER JOIN og_uid og
    ON u.uid = og.uid
    LEFT OUTER JOIN node n
    ON og.nid = n.nid
    WHERE pv.fid = 361 AND pv.value = 114;

    In addition, I'm trying to get the values out of the profile_values table for each user where the fid = 41 (the phone# value).

    Here's what the profile_values field looks like:
    mysql> describe profile_values;
    +-------+------------------+
    | Field | Type |
    +-------+------------------+
    | fid | int(10) unsigned |
    | uid | int(10) unsigned |
    | value | text |
    +-------+------------------+
    3 rows in set (0.00 sec)

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Join the table again with a different alias.

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry Dan, not sure what that means.
    Can you give me a quick example?

    Thanks,
    Kevin

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT u.uid, u.name, u.mail, n.title AS 'group', pvphone.value AS `phone`
    FROM users u 
    LEFT JOIN profile_values pv 
    ON u.uid = pv.uid
    LEFT JOIN profile_values pvphone
    ON pvphone.uid = u.uid AND pvphone.fid = 41
    LEFT OUTER JOIN og_uid og
    ON u.uid = og.uid
    LEFT OUTER JOIN node n
    ON og.nid = n.nid
    WHERE pv.fid = 361 AND pv.value = 114;

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Excellent!
    Thank you!

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Is there a way to do this w/o DISTINCT?

    I think I may be using the wrong join syntax since I'm getting multiple records for each uid unless I add the DISTINCT limiter.

    Am I joining incorrectly? Or is the query, with DISTINCT, correct?

    SELECT DISTINCT(u.uid), u.name, u.mail, n.title AS 'group'
    FROM users u
    LEFT JOIN profile_values pv
    ON u.uid = pv.uid
    LEFT OUTER JOIN og_uid og
    ON u.uid = og.uid
    LEFT OUTER JOIN node n
    ON og.nid = n.nid
    WHERE n.nid = 1786201
    ORDER BY og.created DESC;

    Thanks,
    Kevin

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kmillecam View Post
    Am I joining incorrectly?
    that's hard to say, unless you explain the cardinality of the relationships between your tables

    the thing that really stands out, though, is this --
    Code:
    SELECT DISTINCT(u.uid)...
    i should like to point out that DISTINCT is ~not~ a function

    do yourself a favour and remove the parentheses

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

  8. #8
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks r937

  9. #9
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    that's hard to say, unless you explain the cardinality of the relationships between your tables
    Does this help?

    Code MySQL:
    mysql> DESCRIBE users;
    +------------------+------------------+------+-----+---------+----------------+
    | Field            | Type             | Null | Key | Default | Extra          |
    +------------------+------------------+------+-----+---------+----------------+
    | uid              | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
    | name             | varchar(60)      | NO   | UNI |         |                | 
    | pass             | varchar(32)      | NO   |     |         |                | 
    | mail             | varchar(64)      | YES  | MUL |         |                | 
    | mode             | tinyint(1)       | NO   |     | 0       |                | 
    | sort             | tinyint(1)       | YES  |     | 0       |                | 
    | threshold        | tinyint(1)       | YES  |     | 0       |                | 
    | theme            | varchar(255)     | NO   |     |         |                | 
    | signature        | varchar(255)     | NO   |     |         |                | 
    | created          | int(11)          | NO   | MUL | 0       |                | 
    | access           | int(11)          | NO   | MUL | 0       |                | 
    | status           | tinyint(4)       | NO   | MUL | 0       |                | 
    | timezone         | varchar(8)       | YES  |     | NULL    |                | 
    | language         | varchar(12)      | NO   |     |         |                | 
    | picture          | varchar(255)     | NO   |     |         |                | 
    | init             | varchar(64)      | YES  |     |         |                | 
    | data             | longtext         | YES  |     | NULL    |                | 
    | login            | int(11)          | NO   |     | 0       |                | 
    | timezone_name    | varchar(50)      | NO   |     |         |                | 
    | signature_format | smallint(6)      | NO   |     | 0       |                | 
    +------------------+------------------+------+-----+---------+----------------+
    20 rows in set (0.00 sec)
     
    mysql> DESCRIBE profile_values;
    +-------+------------------+------+-----+---------+-------+
    | Field | Type             | Null | Key | Default | Extra |
    +-------+------------------+------+-----+---------+-------+
    | fid   | int(10) unsigned | NO   | PRI | 0       |       | 
    | uid   | int(10) unsigned | NO   | PRI | 0       |       | 
    | value | text             | YES  |     | NULL    |       | 
    +-------+------------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
     
    mysql> DESCRIBE og_uid;        
    +-----------+---------+------+-----+---------+-------+
    | Field     | Type    | Null | Key | Default | Extra |
    +-----------+---------+------+-----+---------+-------+
    | nid       | int(11) | NO   | PRI | 0       |       | 
    | uid       | int(11) | NO   | PRI | 0       |       | 
    | og_role   | int(1)  | NO   |     | 0       |       | 
    | is_active | int(1)  | YES  |     | 0       |       | 
    | is_admin  | int(1)  | YES  |     | 0       |       | 
    | created   | int(11) | YES  |     | 0       |       | 
    | changed   | int(11) | YES  |     | 0       |       | 
    +-----------+---------+------+-----+---------+-------+
    7 rows in set (0.00 sec)
     
    mysql> DESCRIBE node;  
    +-----------+------------------+------+-----+---------+----------------+
    | Field     | Type             | Null | Key | Default | Extra          |
    +-----------+------------------+------+-----+---------+----------------+
    | nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment | 
    | type      | varchar(32)      | NO   | MUL |         |                | 
    | title     | varchar(255)     | NO   | MUL |         |                | 
    | uid       | int(10)          | NO   | MUL | 0       |                | 
    | status    | int(4)           | NO   | MUL | 1       |                | 
    | created   | int(11)          | NO   | MUL | 0       |                | 
    | changed   | int(11)          | NO   | MUL | 0       |                | 
    | comment   | int(2)           | NO   |     | 0       |                | 
    | promote   | int(2)           | NO   | MUL | 0       |                | 
    | moderate  | int(2)           | NO   | MUL | 0       |                | 
    | sticky    | int(2)           | NO   |     | 0       |                | 
    | vid       | int(10) unsigned | NO   | UNI | 0       |                | 
    | language  | varchar(12)      | NO   |     |         |                | 
    | tnid      | int(10) unsigned | NO   | MUL | 0       |                | 
    | translate | int(11)          | NO   | MUL | 0       |                | 
    +-----------+------------------+------+-----+---------+----------------+
    15 rows in set (0.00 sec)
     
    mysql>

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    so a user can have multiple profile values, and a user can have multiple nodes

    may i ask why your query includes a join to the pv table?

    you're not using it for anything, and i'll bet that's where your dupes are coming from -- a user can have multiple rows in this table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Aug 2004
    Location
    Utah, USA
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah that makes sense.
    When I eliminate the join on the pv table, I don't get duplicates.

    Thanks again for your help r937.


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
  •