0

I can't seem to grasp how I can select records when the records of one user span multiple rows.

Here is the schema of the table.

user_id  key          value
------------------------------------------
1        text         this is sample text
1        text_status  0
2        text         this is sample text
2        text_status  1

from the above table/row you can see that each user has info that has multiple rows. So in this case how do I select say "All the IDs, text value where text_status is "1"?

And to complicate it 1 step further, I need the email address of these accounts which is on another table. How can I write 1 select statement to pull in the email address as well? I know there is a JOIN statement for this but it's a bit complicated for me especially I can't even figure out the first part.

Added Note I must state that this table schema is a Wordpress default table wp_usermeta..

Improve this question
7
  • 1
    Why isn't text_status another field in this table? Commented Mar 28, 2011 at 0:25
  • Can you explain why WHERE key = 'text_status' AND value = '1' won't work? Commented Mar 28, 2011 at 0:26
  • key and value looks wrong. You've designed your table in a way that contravenes the way Database Management Systems are designed to work, which is leading to your problem. Read about database normalization. Commented Mar 28, 2011 at 0:28
  • @OMGPonies: He wants the value in the row where key = "text", for the user_id for whom a row exists with key = "text_status" and value="1". He designed the database wrong. Commented Mar 28, 2011 at 0:29
  • 3
    @Tomalak Geret'kal: Absolutes are dangerous Commented Mar 28, 2011 at 0:39

4 Answers 4

Reset to default
1 
    SELECT t1.*
      FROM tbl t2
INNER JOIN tbl t1 ON t1.user_id = t2.user_id
                 AND t1.key = 'text'
     WHERE t2.key = 'text_status'
       AND t2.value = '1'
Improve this answer
Sign up to request clarification or add additional context in comments.

21 Comments

+1: Might need a DISTINCT for sake of using a JOIN - IN/EXISTS wouldn't require DISTINCT
... AND t1.key = 'text', or you get all rows for matching user_ids.
@OMG Ponies: cannot get, distinct for what?
@Tomalak Geret'kal: yup. But as I said it was put there just for readability purposes. If we move that condition to the WHERE it will not change anything for mysql query optimizer (and execution plan builder).
@Rick: In my query I joined the table tbl to itself. t1 and t2 are just an aliases to the same table so we can refer to it.
|
0

I think you've set up your table incorrectly. Make text_status and value exist within the same row.

The way it is right now, you would have to conduct two queries to get to your end result. Where as, the correct way needs only one.

Improve this answer

3 Comments

I think you're misunderstanding me.
Why does text_status have to be a key, instead of just being a column header?
They already exist in the same row - to get other details related to a particular value (user_id), you have to JOIN/IN/EXISTS. Text_status isn't a column for the same reason text isn't - the OPs setup supports multiple key/value pairs without impacting columns.
0

This arbitrary key:value list scheme is alluring because of its flexibility. But it complicates queries obviously. Depending on the structure of your second table you could get away with:

SELECT key, value FROM user_table WHERE user_id=123
UNION ALL
SELECT 'email' as key, email as value FROM email_table WHERE user_id=123

But that pretty much only returns a list still, not a set of fields.

Improve this answer

Comments

0

key and value looks wrong. SQL already gives you "key" (in the column name) and multiple "values" (in the values given per column in each row).

You've designed your table in a way that contravenes the way Database Management Systems are designed to work, which is leading to your problem. Read about database normalization.

Ideally your table would look something like this:

user_id  text_status   text
------------------------------------------
1        0             this is sample text
2        1             this is sample text

Then your query looks like:

SELECT `user_id`, `text` FROM `table` WHERE `text_status` = '1';

As your table stands now, you'll need something like (untested):

SELECT `table`.*
  FROM `table` LEFT JOIN
       (SELECT `user_id`
          FROM `table`
         WHERE `key` = "text_status"
           AND `value` = "1"
       ) AS `_` USING(`user_id`)
 WHERE `table`.`key` = "text"
Improve this answer

11 Comments

You're not familiar with EAV, and I suggest you read before making statements.
My comment stands as constructive criticism. Your statements have always been to normalize for the sake of it - nothing in them suggests you comprehend what EAV has to offer. I see no reason to give you the benefit of the doubt. If SQL weren't designed for it, why is EAV a recognized design? EAV isn't a preferable practice, but choice depends on design/business requirements - not blindly followed dogma.
Your comment reinforces what I've already said - when exposed to something you don't comprehend, you default to normalization before you took enough time to understand what was presented to you and why the design choice was made.
No, normalization is not always the answer - there are no absolutes, and I've already listed reasons for why in other comments in this question. Your entrenchment is not a sign of strength - it does nothing for your point; it only reinforces that you are a slave to dogma and won't think for yourself. You lack design/development experience, and in the face of constructive criticism react poorly. You have a lot of professional and personal development ahead of you, and I look forward to seeing you improve.
You've had the ability to demonstrate your "knowledge", and all you can do is repeat "normalization" verbatim. Now you want to argue that context matters now, in the face of an established product whose data model can not be changed and one that supports the majority of configuration that a user could envision. All you do is dig yourself deeper.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.