• 0

[mySQL 5] LEFT JOIN problem


Question

I have the following statement used in open atrium (based on drupal)

SELECT *

FROM users u

LEFT JOIN user_status us on((u.uid = us.user_id))

LEFT JOIN status s on((s.status_id = us.user_status))

LEFT JOIN node n on((n.uid = u.uid))

LEFT JOIN content_type_profile p on ((n.nid = p.nid))

WHERE n.type = 'profile';

I want it to show ALL entries in the user table, and append data from the other joins onto that. If there isn't a match, I am ok having a NULL value'd column.

However, it only matches users that have a node.type = 'profile'. If i don't include this however, I get all the entries from the node table, and I don't want that.

Link to comment
Share on other sites

8 answers to this question

Recommended Posts

  • 0

^ Thats the answer, you need to restrict which fields are being pulled with the select statement, so something like:

SELECT `u`.*, `us`.....

...with your refined list of fields.

Link to comment
Share on other sites

  • 0

I actually did that, I removed from the post the huge list of variables incase it confused anyone.

SELECT n.title AS full_name,u.`name` AS username,u.mail AS email,u.picture AS picture,p.field_profile_telephone_value AS telephone,p.field_profile_extension_value AS extension,s.status_id AS status_id,s.status_name AS status_name,s.status_desc AS status_desc,s.status_ref AS status_ref,s.status_code AS status_code,s.menu_order AS menu_order,us.user_id AS user_id,us.user_status AS user_status,us.status_comment AS status_comment,us.updated_at AS updated_at 
   FROM users u	 
   LEFT JOIN user_status us on((u.uid = us.user_id))
   LEFT JOIN status s on((s.status_id = us.user_status))
   LEFT JOIN node n on((n.uid = u.uid))
   LEFT JOIN content_type_profile p on ((n.nid = p.nid))
   WHERE n.type = 'profile';

The problem is that it only returns 4 users (those that have a profile), instead of showing all 12 users in the users table.

For whatever reason, atrium stores the users full name in a node of type profile (that's what this join is for), and there contact details inside content_type_profile.

I am trying to fetch a list of all users, and if this data exists, stick it on, otherwise have null fieds.

Link to comment
Share on other sites

  • 0

neither of those worked i'm afraid.

Is it because i am doing a where on the node table, so if there isn't a node associated with that user, the user does not get included in the query

Link to comment
Share on other sites

  • 0
neither of those worked i'm afraid.

Is it because i am doing a where on the node table, so if there isn't a node associated with that user, the user does not get included in the query

OMG, I didn't even look at that line. You're right.

Link to comment
Share on other sites

  • 0

How can I get around this ?

If I don't include that, it joins all the entries from the node table that a user has posted. (ie far too much data)

Link to comment
Share on other sites

  • 0

Hey guys, I figured the fix:

SELECT n.title AS full_name,u.`name` AS username,u.mail AS email,u.picture AS picture,p.field_profile_telephone_value AS telephone,p.field_profile_extension_value AS extension,s.status_id AS status_id,s.status_name AS status_name,s.status_desc AS status_desc,s.status_ref AS status_ref,s.status_code AS status_code,s.menu_order AS menu_order,us.user_id AS user_id,us.user_status AS user_status,us.status_comment AS status_comment,us.updated_at AS updated_at	  FROM users u		  LEFT JOIN user_status us on((u.uid = us.user_id))	 LEFT JOIN status s on((s.status_id = us.user_status))	 LEFT JOIN node n on((n.uid = u.uid))	 LEFT JOIN content_type_profile p on ((n.nid = p.nid))	 WHERE n.type = 'profile' or n.type IS NULL;

When there is no match in the node table, is returns NULL, so we needed to take that into account :)

Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.