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

Databases
1

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

Join the table again with a different alias.

3

Sorry Dan, not sure what that means.
Can you give me a quick example?

Thanks,
Kevin

4 
SELECT u.uid, u.name, u.mail, n.title AS 'group'[color=red], pvphone.value AS `phone`[/color]
FROM users u 
LEFT JOIN profile_values pv 
ON u.uid = pv.uid
[color=red]LEFT JOIN profile_values pvphone
ON pvphone.uid = u.uid AND pvphone.fid = 41
[/color]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

Excellent!
Thank you!

6

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

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 –

SELECT DISTINCT(u.uid)...

i should like to point out that DISTINCT is ~not~ a function

do yourself a favour and remove the parentheses

:slight_smile:

8

Thanks r937

9

Does this help?

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

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

11

Ah that makes sense.
When I eliminate the join on the pv table, I don’t get duplicates.

Thanks again for your help r937.