83379

`single-row subquery returns more than one row` error unclarity

Question:

I've got two tables. The first one hold a type and the second one the value of this type. In my example if there are more values 'john' as 'first_name' i get:single-row subquery returns more than one row

SELECT DISTINCT id FROM name WHERE id=( SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

I'm not very good with sql. I should use LEFT JOIN or something like that but it's not really clear to me how i should do this.

Answer1:

Apart from the simple fix (using IN instead of id=), you can also use a somewhat simpler version of your query:

SELECT DISTINCT id FROM name WHERE text1='first_name' and id in ( SELECT name_id FROM value WHERE text2='john')

Answer2:

Since subquery [can] returns multiple values, IN is better use than =

SELECT DISTINCT id FROM name WHERE id IN ( SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

IN is equivalent for OR, example:

SELECT * FROM tableName WHERE a = 4 or a = 5 or a = 6

can be written as

SELECT * FROM tableName WHERE a in (4,5,6)

The = (equal sign) is used to assign single value.

Answer3:

SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

This query returns more than one row, then you should use IN clause like here

SELECT DISTINCT id FROM name WHERE id IN ( SELECT id FROM name WHERE text1='first_name' INTERSECT SELECT name_id FROM value WHERE text2='john');

Or if you expect only a row, you have to review your DB's logic and behaviour.

Answer4:

you could just change WHERE id= to WHERE id IN

Recommend

  • How can i check if the table in oracle has garbage value in its some column/row?
  • How to correctly set AUTO INCREMENT fo a column in SQLite, using Python?
  • How to get text in a selected row in ListView, if ListView got item and subitem
  • Scroll multiple TextViews simultaneously
  • Why these “span” inside an “a” fail using IE7?
  • Phaser HTML5 app cannot play sound after porting by Phonegap Cloud Build
  • how to change the font size of all text box within a grid, windows app uwp
  • Textview display in Android from sqlite
  • Formatting Integer with a decimal point
  • Check if focus is on a textfield
  • JS: how can I base64 encode a local file without XMLHttpRequest?
  • How to setup night mode drawables to work as expected
  • PreparedStatement - how specify to use default value of column
  • Scala FlatMap provides wrong results
  • jquery change text on div
  • Position Userform differently for each ActiveCell Clicked
  • How to intersect multiple polygons?
  • Bind two JSF input fields with single property of backing bean
  • Intersect on ArrayList with objects groovy
  • Replace HTML links with text
  • Socket io in node app on google app engine
  • Taking mean across rows grouped by a variable in numpy
  • Grouping by blank nodes
  • Query to get the Top 2 from each group
  • How to implement limit with Nhibernate and Sybase
  • Passing 1 of 3 values to a Sub
  • Django: Count of Group Elements
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • ORA-29908: missing primary invocation for ancillary operator
  • How to get next/previous record number?
  • How to delete a row from a dynamic generate table using jquery?
  • json Serialization in asp
  • Rails 2: use form_for to build a form covering multiple objects of the same class
  • python regex in pyparsing
  • How do you join a server to an Active Directory (domain)?
  • How to stop GridView from loading again when I press back button?
  • How does Linux kernel interrupt the application?