mysql query which selects from another mysql query

Question

i am migrating from ms access database to mysql database with java frontend so that my application can be used in linux as well .

in ms access here's what i would do go to create query . write a select statement . call give the name of the query as query1. when double clicking on query1 you would get the result of select statement in a tabular form. next i would write a query2 which is also a select query. this query would be fetching data not from a table but query1 e.g select a,b from query1;

now i am on a mysql database using java what would be the java statement for select a,b from query1 ?

what i mean to say is that i would connect to mysql using jdbc. have query1 like this

string query1 = " select * from users " ;

then execute query using executeQuery(query1)

but i dont think i can do something like this.

string query2 = " select a,b from query1 " ;

and then executeQuery(query2)

so what is the way out ?

Answer 1

select * from table2 where user_id in (select user_id from users)

Answer 2

You can do this all in MySQL. The query would look like

SELECT * FROM (
 SELECT * FROM users
) query1;

Answer 3

You can either do nested queries (subqueries) like @muffinista suggested.

But i think you are looking for Views: http://dev.mysql.com/doc/refman/5.0/en/create-view.html.

In short, a view is a "pseudo table" that is a result of a query.

you can

create view q1 as 
select * from table1 where f1>1

select * from q1 where f2<100

Answer 4

I ran into the exact same problems when I went from using MS Access to using a lot of SQL queries against a MySQL DB.

There are two ways I would approach this:

VIEWS:

Views are a great way to emulate a lot of the functionality you found in Access. One of the things I really liked about Access was the ability to separate my SQL into smaller queries and then re-use those queries in other queries. Views allow you to do essentially the same thing in that you define a query in a View and then you can write another query or View against that original View.

In my experience, however, Views tend to be really slow, especially when referencing calculated columns. With MySQL, I very rarely use Views (though perhaps others have found for efficient ways of implementing them).

SUBQUERIES (NESTED QUERIES)

As others have mentioned, subqueries are a great way to write multiple queries within one query. With a subquery, instead of putting the query name (as in Access) or View name (as explained above) within the SELECT portion of your code, you simply paste the entire SQL statement of the subquery.

You might write code like this to find only the 2009 sales and salesperson name for customers in a database:

SELECT
customer.Name,
customer.AccountNumber,
customer.SalespersonName,
ch.`2009 Sales`
FROM
customer
Left Join (
    SELECT
    customerhistory.AccountNumber,
    SUM ( CASE WHEN customerhistory.`Year` = 2009 
               THEN customerhistory.`Sales` 
               ELSE 0 
               END
        ) AS `2009 Sales`
    FROM
    customerhistory
    GROUP BY
    customerhistory.AccountNumber
) ch ON customer.AccountNumber = ch.AccountNumber

In my work, I tend to use mostly subqueries since I find they run a lot faster than views, but your experience may vary.