×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

lost in querying

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)
 

RE: lost in querying

GROUP BY and MAX?

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

RE: lost in querying

(OP)
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

RE: lost in querying

Did you even try my suggestion ?

RE: lost in querying

(OP)
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

RE: lost in querying

(OP)
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?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close