Complex query returning one row from multiple rows

[JasonDFR]

Hello all. Thanks for the help!

Two tables:

Table `posts` holds 'p_id', 'post', 'u_id'

Table `users_profile` holds 'u_id', 'profile_key', 'value'

How do I return one row for each 'p_id' that includes all the information in the `posts` table as well as the information in the `users_profile` table WHERE `posts`.`u_id` = `users_profile`.`u_id` ?

I would also like for the 'profile_key' to become the key for the 'value' when I use $row = mysql_fetch_assoc($r); in my php script.

The problem is that `u_id` in the `users_profile` table is not unique. The table is structured like:

[U]u_id[/U]   [U]profile_key[/U]   [U]value[/U]

1      first_name    Jason
1      last_name     Smith
2      first_name    Sam
1      birth_date     01/01/1970
2      etc               etc

Hope someone can help me. I know it would be simpler to structure the users_profile table with one row for each 'u_id' but I like the idea of doing it as shown above so there would be a potential to have unlimited profile_keys for every user.

p_id = "post id"
u_id = "user id"

Thanks a lot. I have learned a lot from these forums. They are great!

[dickersonka]

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

[dickersonka]

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

[JasonDFR]

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.

[dickersonka]

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

[JasonDFR]

A stored procedure would be fine.

[dickersonka]

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

[dickersonka]

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

[dickersonka]

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;

[JasonDFR]

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

[JasonDFR]

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!

[JasonDFR]

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.

[dickersonka]

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

[JasonDFR]

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

[dickersonka]

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