SQL get multiple values (from the same column) from same table using multiple queries

Question

I am trying to get a list of values from the same column in a table by running two queries.

This is what the table looks like:

******************************************
 Key | Short_text | UID | Boolean_value
******************************************
Name | John       | 23  | null
******************************************
Male | NULL       | 23  | true
******************************************
Name | Ben        | 45  | null
******************************************
Male | NULL       | 45  | true

I am trying to get the SHORT_TEXT of the NAME rows if the Boolean values of the Male rows are true based on the UIDs

This is what I have so far (Which is throwing an error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. )

  SELECT SHORT_TEXT_VALUE
  FROM Table
  WHERE ((SELECT UID
  FROM Table
  WHERE KEY = 'NAME') =
 (SELECT CUSTOMER_UID
  FROM Table
  WHERE KEY = 'Male'
  AND BOOLEAN_VALUE = 1))

I am very new to sql so I am not sure what I should do to achieve what I would like.

Any help would greatly be appreciated.

I suggest you think about redesigning your table. You don't appear to have a primary key field, and you're running into problems like this one. If your table had columns "uid","name,"gender", "short_text" your query would be trivially easy... — mlinth
– mlinth, Commented Nov 6, 2014 at 7:46
@mlinth the only advantage i can think of for his table design is for custom dynamic fields where each UID may or may not contain a specific key (name,male,address....) but that would cause major validation problem as well since key information is not complete. — TheProvost
– TheProvost, Commented Nov 6, 2014 at 7:51

fthiella · Accepted Answer · 2014-11-06 07:55:13Z

2

You can join your table with itself:

SELECT
  t1.UID,
  t1.Short_text
FROM
  tablename t1 INNER JOIN tablename t2
  ON t1.UID=t2.UID
WHERE
  t1.Key='Name' AND t2.Key='Male' AND t2.Boolean_value=TRUE

or this with EXISTS:

SELECT
  t1.UID,
  t1.Short_text
FROM
  tablename t1
WHERE
  t1.Key='Name' AND
  EXISTS (SELECT * FROM tablename t2
          WHERE t1.UID=t2.UID AND t2.Key='Male' AND t2.Boolean_value=1)

edited Nov 6, 2014 at 7:55

answered Nov 6, 2014 at 7:43

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Lilu Patel Over a year ago

There is only 1 table. Does this query join on 2 tables?

fthiella Over a year ago

@LiluPatel this query joins 1 table with itself, that's why I'm using two different aliases (t1 and t2)

TheProvost · Accepted Answer · 2014-11-06 07:55:56Z

1

I am unsure what you are trying to accomplish but basing on your code I think this is what you want

 SELECT SHORT_TEXT_VALUE
  FROM Table
    WHERE KEY='Name' 
    and UID in(SELECT UID
  FROM Table
  WHERE KEY = 'Male'
  AND BOOLEAN_VALUE = 1)

But on a more important note. You might want to think about your redesigning your table design. Why is Male details of a specific uid on a different row?

edited Nov 6, 2014 at 7:55

answered Nov 6, 2014 at 7:45

TheProvost

1,9133 gold badges18 silver badges42 bronze badges

3 Comments

Lilu Patel Over a year ago

Its a bit more complicated. This table was designed by someone else and I agree it is terrible!

Lilu Patel Over a year ago

There are other rows with other keys, I just wanted the row with the 'Name' key. How could I add this to the query?

TheProvost Over a year ago

Updated my answer. Thought so. Was going to ask you about that after because im pretty sure this table design is for multiple keys. hope this helped you :)

Giwwel · Accepted Answer · 2014-11-06 07:55:03Z

1

Hi try it with a subquery, try this:

SELECT Short_text
FROM table
WHERE uid in (SELECT uid FROM table WHERE boolean_value = "true")
AND Short_text IS NOT NULL

Make sure that the values of the Male rows are(NULL) and not the string with "NULL"

Btw. This table does not match to the normalization-form of database-tables. Please read the introduction to database normalization

answered Nov 6, 2014 at 7:55

Giwwel

3471 silver badge4 bronze badges

Collectives™ on Stack Overflow

SQL get multiple values (from the same column) from same table using multiple queries

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related