9765

Cardinality violation when using a subquery that returns two values

Question:

I have create a sql query that the sketch is like this

select * from A where A.id in (select B.id1, B.id2 from B);

where the main select returns those values for which A.id coincides with either B.id1 or B.id2.

Clearly this solution doesn't work as the cardinality doesn't match in the where clause. How can I overcome this problem?

One solution would be to make two sub-queries, one for B.id1 and one for B.id2, but as my sub-query is much longer than in this example I was looking for a more elegant solution.

I'm using Mysql

<strong>EDIT 1</strong> As long as the syntax is simpler than using two sub-queries I have no issues using joins

<strong>EDIT 2</strong> Thanks @NullSoulException. I tried the first solution and works as expected!!

Answer1:

Something like the below should do the trick.

select * From table1 a , (select id1 , id2 from table2 ) b where (a.id = b.id1) or (a.id = b.id2)

or you can JOIN with the same table twice by giving the joined tables an alias.

select * from table1 a INNER JOIN table2 b1 on a.id = b1.id1 INNER JOIN table2 b2 on a.id = b2.id2

Please test the above against your datasets/tables..

Recommend

  • Groovy: Add some element of list to another list
  • How to delete an associated object in Entity Framework without having access to the object context
  • Doctrine2 subquery
  • TypeError: a float is required in sklearn.feature_extraction.FeatureHasher
  • Aspect ratio behaviour of - internal dimensions and element size
  • Getting wrong values for other columns when I select MAX(updated_date)
  • SQL Server - Is there a collation that provides natural order for numbers?
  • Use CASE statement with SUM function in SQL Server
  • what does the follow prolog codes do?
  • SQL Server Like Query not case sensitive
  • Level-order tree traversal
  • Does sql server minds the way records where inserted?
  • SQL append distinct values from two columns and two tables
  • How to detect left mouse click but not when the click occur on a UI Button component [closed]
  • command line of process by name
  • SQL Server re-calculate or not?
  • Does the MySQL IN clause execute the subquery multiple times?
  • TSQL Rolling Average of Time Groupings
  • Cypher - matching two different possible paths and return both
  • MySQL performance when updating row with FK
  • Wrong labels when plotting a time series pandas dataframe with matplotlib
  • Which open source license has no forking [closed]
  • How does this usort cmp function actually work?
  • What and where is mdimport
  • Alternative To body {overflow:scroll;} That Will Prevent Page Jostling/Wriggling?
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Extracting HTML between tags
  • MongoDB in PHP using aggregate to group by _id is null not working
  • Why HTML5 Canvas with a larger size stretch a drawn line?
  • Check if a string to interpolate provides expected placeholders
  • Convert array of 8 bytes to signed long in C++
  • R: gsub and capture
  • RestKit - RKRequestDelegate does not exist
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Traverse Array and Display in markup
  • Comma separated Values
  • Understanding cpu registers
  • Recursive/Hierarchical Query Using Postgres
  • Running Map reduces the dimensions of the matrices
  • How to load view controller without button in storyboard?