lost in querying
lost in querying
(OP)
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)
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)
RE: lost in querying
Do you want to detect if a user has specified both 1 and 2 as gender?
RE: lost in querying
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: lost in querying
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
RE: lost in querying
RE: lost in querying
If yes then you may try something like this:
CODE
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: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?
RE: lost in querying
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
RE: lost in querying
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?