Merge multiple rows to one

Question

I have the following query, but this one returns 3 rows and i want one row ;-)

SELECT 
b_firstname ,value
FROM
cscart_user_profiles
RIGHT JOIN profile_fields_data ON profile_fields_data.object_id =    user_profiles.profile_id
WHERE 
user_profiles.b_title NOT LIKE ''
AND user_profiles.profile_id = '4252'
AND (
    profile_fields_data.field_id ='69' 
    OR 
    profile_fields_data.field_id ='73' 
    OR 
    profile_fields_data.field_id ='75'
)

... but this will return 3 rows:

user1 value
user1 value
user1 value

I want 1 row:

user1 value69 user73 value75

How can I solve this?

Below the 2 tables where the data is

 CREATE TABLE IF NOT EXISTS `cscart_user_profiles` (
  `profile_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `profile_type` char(1) NOT NULL DEFAULT 'P',
  `b_title` varchar(32) NOT NULL DEFAULT '',
  `b_firstname` varchar(128) NOT NULL DEFAULT '',
  `b_lastname` varchar(128) NOT NULL DEFAULT '',
  `b_address` varchar(64) NOT NULL DEFAULT '',
  `b_address_2` varchar(64) NOT NULL DEFAULT '',
  `b_city` varchar(64) NOT NULL DEFAULT '',
  `b_county` varchar(32) NOT NULL DEFAULT '',
  `b_state` varchar(32) NOT NULL DEFAULT '',
   ......
  PRIMARY KEY (`profile_id`),
  KEY `uid_p` (`user_id`,`profile_type`),
  KEY `profile_type` (`profile_type`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=0 ;

CREATE TABLE IF NOT EXISTS `cscart_profile_fields_data` (
  `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `object_type` char(1) NOT NULL DEFAULT 'U',
  `field_id` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `value` varchar(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`object_id`,`object_type`,`field_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Consider posting your database schema and sample data.

Minesh
– Minesh

2013-02-15 13:30:08 +00:00
Commented Feb 15, 2013 at 13:30 — Minesh
– Minesh, Commented Feb 15, 2013 at 13:30

Tom · Accepted Answer · 2013-02-15 14:55:58Z

1

You could join to the profile_fields_data table 3 separate times to get the 3 separate values on a single row.

SELECT  b_firstname, 
        pfd69.value as value69, 
        pfd73.value as value73, 
        pfd75.value as value75 

FROM    cscart_user_profiles AS up

        RIGHT JOIN cscart_profile_fields_data AS pfd69
        ON pfd69.object_id = up.profile_id      
        AND pfd69.field_id ='69'

        RIGHT JOIN cscart_profile_fields_data AS pfd73
        ON pfd73.object_id = up.profile_id      
        AND pfd73.field_id ='73'

        RIGHT JOIN cscart_profile_fields_data AS pfd75
        ON pfd75.object_id = up.profile_id      
        AND pfd75.field_id ='75'

WHERE   up.b_title NOT LIKE ''
        AND up.profile_id = '4252'

edited Feb 15, 2013 at 14:55

answered Feb 15, 2013 at 13:34

Tom

6,6633 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bas Over a year ago

one right join works fine, but 3 not. I am getting this error: #1066 - Not unique table/alias: 'cscart_profile_fields_data'

Tom Over a year ago

Try again now. In your original SQL you were joining to the profile_fields_data table, not cscart_profile_fields_data. I updated my SQL. I also fixed an alias issue with my where clause.

Simon Martin · Accepted Answer · 2013-02-15 13:32:23Z

0

Can you use nested queries?

SELECT * FROM (
   SELECT User1, Value69 FROM table ) AS T1
INNER JOIN (
   SELECT User2, Value75 FROM table ) AS T2 ON T1.Somevalue = T2.Somevalue

etc.

But without seeing your schema it's hard to write a helpful example

answered Feb 15, 2013 at 13:32

Simon Martin

4,2317 gold badges60 silver badges95 bronze badges

Collectives™ on Stack Overflow

Merge multiple rows to one

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related