lost in querying

[vengelus82]

Dear all,

this is my following problem:

28035,"104"
28035,"31"
28035,"39"
28035,"75"
28035,"2"
28035,"86"
28035,"44"
28035,"84"
28035,"98"
476,"1"
476,"76"
476,"40"
476,"31"
476,"102"
476,"93"
476,"53"
476,"83"
476,"96"
6697,"76"
6697,"1"
6697,"41"
6697,"32"
6697,"102"
6697,"94"
6697,"83"
6697,"96"

the first column are my users and the second column is the attribute they have in their profile. This profile is voluntarily added by users.

1 and 2 represent gender attribute, as you can see, these attributes are recorded in the table in different order

I have the following mysql query to extract them and this is the following what I get

Query
DROP TABLE IF EXISTS temp_gender;
CREATE TABLE temp_gender(
select
user_profiles.user_id,
(CASE WHEN user_profiles.profile_id = 1 THEN 1 WHEN user_profiles.profile_id = 2 THEN 2 ELSE user_profiles.profile_id = 0 END) AS 'gender'
FROM user_profiles);

28035,"0"
28035,"0"
28035,"0"
28035,"0"
28035,"2"
28035,"0"
28035,"0"
28035,"0"
28035,"0"
476,"1"
476,"0"
476,"0"
476,"0"
476,"0"
476,"0"
476,"0"
476,"0"
476,"0"
6697,"0"
6697,"1"
6697,"0"
6697,"0"
6697,"0"
6697,"0"
6697,"0"
6697,"0"
2397,"1"
2397,"0"
2397,"0"
2397,"0"
2397,"0"
2397,"0"
2397,"0"
2397,"0"
2397,"0"

if I would use

DROP TABLE IF EXISTS temp_gender;
CREATE TABLE temp_gender(
select
DISTINCT user_profiles.user_id,
(CASE WHEN user_profiles.profile_id = 1 THEN 1 WHEN user_profiles.profile_id = 2 THEN 2 ELSE user_profiles.profile_id = 0 END) AS 'gender'
FROM user_profiles

Then I get

"28035","0"
"476","0"
"6697","0"
"2397","0"

if I use
DROP TABLE IF EXISTS temp_gender;
CREATE TABLE temp_gender(
select
user_profiles.user_id,
(CASE WHEN user_profiles.profile_id = 1 THEN 1 WHEN user_profiles.profile_id = 2 THEN 2 ELSE 0 END) AS 'gender'
FROM user_profiles
GROUP BY user_profiles.user_id);

Then I get
"28035","0"
"476","1"
"6697","0"
"2397","1"

And finally when I use

DROP TABLE IF EXISTS temp_gender;
CREATE TABLE temp_gender(
select
distinct user_profiles.user_id,
(CASE WHEN user_profiles.profile_id = 1 THEN 1 WHEN user_profiles.profile_id = 2 THEN 2 ELSE 0 END) AS 'gender'
FROM user_profiles);

I get
"28035","0"
"28035","2"
"476","1"
"476","0"
"6697","0"
"6697","1"
"2397","1"
"2397","0"

the question is

How would I need to change my statement to get

"28035","2"
"476","1"
"6697","1"
"2397","1"
"13397","0"(this user did not fill in his/her gender)

 

[JarlH]

GROUP BY and MAX?

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

 

[PHV]

What about this ?

SELECT user_id, profile_id
FROM user_profiles
WHERE profile_id IN (1,2)
UNION SELECT user_id, 0
FROM user_profiles
WHERE user_id NOT IN (SELECT user_id FROM user_profiles WHERE profile_id IN (1,2))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vengelus82]

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

 

[PHV]

Did you even try my suggestion ?

 

[PHV]

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

SELECT U.user_id, COALESCE(G.profile_id,0) AS gender
FROM user U LEFT JOIN (
SELECT user_id, profile_id FROM user_profiles WHERE profile_id IN (1,2)
) G ON U.user_id = G.user_id

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vengelus82]

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

 

[vengelus82]

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?