Complex query returning one row from multiple rows

select p.p_id, post, p.u_id, u.profile_key, u.value
from posts p
inner join users_profile u
on p.u_id = u.u_id

sorry forgot to look at your row structure, give me a few and i'll post back

Yeah, this one is tough. I hope you can figure it out.

Here is the structure:

-- Structure de la table `users_profile`
--

CREATE TABLE IF NOT EXISTS `users_profile` (
  `u_id` int(10) unsigned NOT NULL,
  `profile_key` varchar(255) collate utf8_unicode_ci NOT NULL,
  `profile_value` text collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`u_id`,`profile_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Contraintes pour les tables exportées
--

--
-- Contraintes pour la table `users_profile`
--
ALTER TABLE `users_profile`
  ADD CONSTRAINT `users_profile_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `users` (`u_id`);

Problem is when you have ONE post with a u_id, but you have many rows in the users_profile table with the same u_id.

So even though I only want one row with one post, I get a bunch of rows because the u_id appears in a bunch of rows of the users_profile table.

I know this is a pretty advanced query, so I really appreciate the help.

are you able to use a stored procedure, or must it be a select only?

A stored procedure would be fine.

DELIMITER $$

CREATE PROCEDURE `p_GetUser`(v_U_ID int)
BEGIN

select u_id,
(select value from users_profile where profile_key = 'first_name' and u_id = v_U_ID) as first_name,
(select value from users_profile where profile_key = 'last_name' and u_id = v_U_ID) as last_name,
(select value from users_profile where profile_key = 'birth_date' and u_id = v_U_ID) as birth_date
from users_profile
where
u_id = v_U_ID
group by u_id;
end

working on a single select right now rather than a proc, i'll see how much time i have to finish

here's a select, just add where u_id = ?

select u_id,
max(if(profile_key='first_name', VALUE, null)) as first_name,
max(if(profile_key='last_name', VALUE, null)) as last_name,
max(if(profile_key='birth_date', VALUE, null)) as birth_date,
max(if(profile_key='etc', VALUE, null)) as birth_date
FROM users_profile
group by u_id;

I'll try to get it working tomorrow morning. It's after 10PM here and I have been in front of the computer all day.

I really need to learn the theory behind these kinds of queries.

Thank you very much and perhaps we can talk more later.

Take care,

Jason

SELECT
max(if(profile_key='first_name', profile_value, null)) as first_name,
max(if(profile_key='last_name', profile_value, null)) as last_name
FROM `users_profile`
WHERE `u_id` = 1
GROUP BY `u_id`;

Check out the above. It works!

Now I just need to structure the query to join another table to it and return the columns I want as one row.

I understand why you used 'if', but I am not understanding why 'max' is important.

Thanks a lot!

This allows me to join all the columns in another table ('posts' in this case):

SELECT a.*,
max(if(profile_key='first_name', profile_value, null)) as first_name,
max(if(profile_key='last_name', profile_value, null)) as last_name
FROM `posts` AS a, `users_profile` AS b
WHERE a.u_id = b.u_id
GROUP BY `u_id`
LIMIT 5 ;

This is awesome stuff!

The only thing better would be if it was possible to return all of the 'profile_keys' in the users_profile table that exist without having to explicitly name them.

Thanks again and if you have any more ideas or information about these kind of queries, please make another post. It is extremely hard to find good, clear information about advanced sql like this.

the reason why you have to use max is the other rows will have null in them, you are able to get all the profile keys with dynamic sql, doubt you want to go that route though

Someone else I talked to about this mentioned dynamic sql. This is creating the query dynamiclly, right?

What would be the disadvantage to that?

Thanks again!

Jason

the disadvantage is its slower, and you can't tell the query being executed until runtime, everything is in a string

it looks like

DECLARE sqls varchar(4000);
SET sqls = CONCAT_WS('SELECT * FROM table WHERE ID=','4');
EXECUTE(sqls);

i'm sure there are some grammar mistakes in there but thats the concept, just messy normally unless you need it