By Wyze1 on

Hi guys

I'm trying to select the following

fid from friends table,

uid, picture from the users table

value, fid from the profile_values table

when executing this query
SELECT f.fid, u.uid, u.picture, p.value, p.fid
FROM friends f
INNER JOIN users u ON f.fid = u.uid
INNER JOIN profile_values p ON f.fid = p.uid
WHERE f.fid =2
AND p.fid >=2

Its returns two rows and like this:
fid| uid | picture | value | fid
1 | 1 |img | Name | 1
1 | 1 |img | Surname | 1

How do I edit my query to do this:

fid| uid | picture | value1 | value2

Im not really that good with mysql so sorry!

⋅ Categories: Drupal 6.x

Comments

kpander’s picture

kpander commented

Hey,

If I'm understanding what you want to do, it's as simple as changing this:

SELECT f.fid, u.uid, u.picture, p.value, p.fid

to this

SELECT f.fid AS value1, u.uid, u.picture, p.value, p.fid AS value2

Is that what you were looking for?

Kendall
Abandoned Industry! :: http://invisiblethreads.com/galleries ::

Wyze1’s picture

Wyze1 commented

Hi Kendall

It still returns two rows instead of one! what I want to do is instead of this:

1 | 1 |img | Name | 1
1 | 1 |img | Surname | 1

This query return two rows with the same values and only the Name and Surname Differ
What I want to do is either merge Name And Surname according to the uid into value or create a temp placeholder that only displays Surname
next to Name

In Open Source I Trust! Everybody Else Must Pay Cash!

zeta ζ’s picture

zeta ζ commented 
SELECT f.fid, u.uid, u.picture, GROUP_CONCAT(p.value SEPARATOR ' ') AS name
FROM friends f
INNER JOIN users u ON f.fid = u.uid
INNER JOIN profile_values p ON f.fid = p.uid
WHERE f.fid =2
AND p.fid >=2
GROUP BY f.fid, u.uid, u.picture

___________________
It’s in the detaιls…

Wyze1’s picture

Wyze1 commented

Now that rocks!!!

Thanks

Do you mind explaining what you did?

In Open Source I Trust! Everybody Else Must Pay Cash!

zeta ζ’s picture

zeta ζ commented

If you have a query that returns several rows for each ‘object’, whereas you only want one row per ‘object’;-

  • List all fields that return the same value for each of the rows that refers to that ‘object’, in a GROUP BY clause.
  • Decide what you want to do with the values that differ, and choose the appropriate aggregate function.
  • If you only want certain ‘objects’, use a HAVING clause to specify which ones (not WHERE clause).

___________________
It’s in the detaιls…