Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Question about SQL Query

 
Fred Victa
Ranch Hand
Posts: 189
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Suppose this query returns more than one row:
SELECT number_of_sales FROM myTable1 WHERE state = 'CA';

Suppose number_of_sales is a column found in these two tables: myTable1 and myTable2.

How would you write an SQL query to do the following?

SELECT * FROM myTable2 WHERE number_of_sales = number_of_sales returned from SELECT number_of_sales FROM myTable1 WHERE state = 'CA';
 
Tim Cooke
Sheriff
Pie
Posts: 3203
142
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You could do that with a subquery, and in your particular case using an IN clause.
 
Roel De Nijs
Sheriff
Posts: 10662
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Probably an inner join might work as well.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4031
18
Eclipse IDE Flex Google Web Toolkit
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I recommend always using an INNER JOIN if possible... some DBMS's do not optimize nested subqueries well.
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott Selikoff wrote:I recommend always using an INNER JOIN if possible... some DBMS's do not optimize nested subqueries well.

Absolutely.

Also, joins are the standard way to do this in SQL, so your code will be much easier for other people to read and to maintain.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic