GROUP BY and MAX?

Do you want to detect if a user has specified both 1 and 2 as gender?
 

What about this ?

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Well the first part of the statement works, the second part doesn't...Maybe something I left out. if user decide not to fill in, it doesn't get recorded So there is no

user_id gender
"13397","0"

this row doesn't exist in table...but I do want to be retrieved when I query for the whole list

Did you even try my suggestion ?

Do you have a user table ?
If yes then you may try something like this:

Hope This Helps, PH.
FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Sorry for late reply. Had the flu and wasn't feeling too well to reply...First of all, thanks for all your answers and so soon. Unfortunately, the queries couldn't be successfully applied. Instead I use the suggestion provided by JarlH as follow

SELECT
    user_profiles.user_id,
    MAX(CASE WHEN user_profiles.profile_id = 1 THEN 1 WHEN user_profiles.profile_id = 2 THEN 2 ELSE user_profiles.profile_id IS NULL END) AS 'gender',
    Date_format(user_profiles.date, '%Y/%m/%d')AS date,
FROM user_profiles
GROUP BY user_profiles.user_id

This works, thank you for that. I applied to the different categories and manipulated a further bit and now I have a multi-dimensional table...thank you for all your help and suggestion nevertheless

A few followup questions:

I have successfully generate 9 categories tables and also successfully combined them into 1 table(see as follow):

DROP TABLE IF EXISTS user_profiles;
CREATE TABLE user_profiles (
SELECT temp_gender.user_id, temp_gender.gender, temp_age.age, temp_education.education, temp_branch.branch,
        temp_work.work, temp_home.home, temp_housetype.housetype, temp_state.state, temp_children.children, temp_gender.date
FROM temp_gender INNER JOIN temp_age ON temp_gender.user_id = temp_age.user_id
        INNER JOIN temp_education ON temp_age.user_id = temp_education.user_id
        INNER JOIN temp_branch ON temp_education.user_id = temp_branch.user_id
        INNER JOIN temp_work ON temp_branch.user_id = temp_work.user_id
        INNER JOIN temp_home ON temp_work.user_id = temp_home.user_id
        INNER JOIN temp_housetype ON temp_home.user_id = temp_housetype.user_id
        INNER JOIN temp_state     ON temp_housetype.user_id = temp_state.user_id
        INNER JOIN temp_children ON temp_state.user_id = temp_children.user_id
GROUP BY temp_gender.user_id);

The first question is: It takes a damn long while to execute this statement. Is there any way to rewrite this query that will shorten up the execution time?

The second question involve as follow:

INSERT INTO TABLE user_profiles(user_id, gender, age, education, branch, work, home, housetype, state, children, date)
VALUES
(SELECT temp_gender.user_id, temp_gender.gender, temp_age.age, temp_education.education, temp_branch.branch,
        temp_work.work, temp_home.home, temp_housetype.housetype, temp_state.state, temp_children.children, temp_gender.date
FROM temp_gender LEFT JOIN temp_age ON temp_gender.user_id = temp_age.user_id
        LEFT JOIN temp_education ON temp_age.user_id = temp_education.user_id
        LEFT JOIN temp_branch ON temp_education.user_id = temp_branch.user_id
        LEFT JOIN temp_work ON temp_branch.user_id = temp_work.user_id
        LEFT JOIN temp_home ON temp_work.user_id = temp_home.user_id
        LEFT JOIN temp_housetype ON temp_home.user_id = temp_housetype.user_id
        LEFT JOIN temp_state     ON temp_housetype.user_id = temp_state.user_id
        LEFT JOIN temp_children ON temp_state.user_id = temp_children.user_id
WHERE user_id > 146798)

UPDATE TABLE user_profiles
SET user_profiles_2.gender = WHEN user_profiles.profile_id = 1 THEN 1 WHEN user_profiles.profile_id = 2 THEN 2 ELSE user_profiles.profile_id IS NULL END
WHERE user_profiles_2 LEFT JOIN temp_gender ON user_profiles.user_id = temp_gender.user_id

I want to insert and update the table: Is this done correctly?