Make Another Query Using the existing result set.

Using LAMP

Table #1 looks like this:                                 Table #2 looks like this:

Record #      goodOrBad                                 Record #   User Name
1                    good                                          1                moe
2                    bad                                            2                larry
3                    good                                          3                 curly
4                    good                                          4                 shemp

So I could issue a query 'give me the record # of all the "good" records'. The results would be
1, 3, and 4.

I want to issue a second query using the results of the first query. I want the user names with a "good" record. So the result in this case would be: moe, curly, shemp.

What's the query I need for this? (It could be a single query or two sequential queries)

LVL 3
rfr1tzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lozlozCommented:
$query = "SELECT t2.username FROM table1 t1, table2 t2 WHERE t1.goodOrBad = 'good' AND t1.recordid = t2.recordid";

you'll need to change some column/table names there of course. i don't particuarly understand why you'd want two tables for this since there is a one to one relationship between the data meaning they can be stored in one table, unless you can't change the tables of course

loz
0
rfr1tzAuthor Commented:
Maybe I over-simplified the question. But how about if I had the result 1,3,4 already sitting there and I wanted to get the corresponding user names?

Conceptually

select username from table2 where record=1 or record=3 or record=4. But what if there are 1000 records. Then this simple method will be infeasible.

is there a  "where record is in resultset" construction?
0
lozlozCommented:
i don't quite understand what you mean by already sitting there - my query above will work for 1000 records if the records you want to select are associated with 'good'

loz
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

aolXFTCommented:
Record #      goodOrBad                                 Record #   User Name
1                    good                                          1                moe
2                    bad                                            2                larry
3                    good                                          3                 curly
4                    good                                          4                 shemp

let me get this straight, from the above table, moe, curly, and shemp are good, but larry is bad?

And you want to get all the good entries?

Lets say that the good/bad table is called goodorbad, and has the tablenames user_id, and goodorbad as column names.

This would be created in mysql with "CREATE TABLE goodorbad (user_id int, goodorbad char(4))" (oversimplification, I'd always add in an id with a Primary Key, but for illustration purposes)

and your 'users' table would be created with "CREATE TABLE users (user_id, username varchar(20))", or something like that.

For that I'd use

select u.* from users u left join goodorbad g on u.user_id=g.user_id where g.goodorbad="good"
0
snoyes_jwCommented:
While the other answers might actually be more efficient, here's what I think you're trying to do:

SELECT user_name FROM users WHERE record_num IN (insert_comma_separated_list_here);
0
rfr1tzAuthor Commented:
yes, this left join seems right. I'll have to study this a while. I can't read the syntax. I'll get back top you when I decipher it.
0
snoyes_jwCommented:
If you have version 4.1 or later, you can replace that comma separated list with a subquery:
SELECT user_name FROM users WHERE record_num IN (SELECT record_num FROM goodOrBad WHERE blah blah blah);

Which you use is up to which version of mySQL you have, which you find most readable, and possibly which method your benchmarking reveals to be fast enough for you (it doesn't matter which one is technically 4 CPU cycles faster per record if you are querying less than a million records).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lozlozCommented:
i don't see what's wrong with my method :p

$query = "SELECT t2.username FROM table1 t1, table2 t2 WHERE t1.goodOrBad = 'good' AND t1.recordid = t2.recordid";

table1 (a.k.a t1) is table #1 in the question, table2 (a.k.a t2) is table #2 in the question, the column to take out is t2.username and the conditions are that record # in each table is the same and that the column goodOrBad in table1 is good

loz
0
snoyes_jwCommented:
The only difference result-wise between lozloz and aolXFT is that using a LEFT JOIN will return "NULL" if there is no matching goodOrBad entry, whereas the implicit join which lozloz mentions will not include rows where there is no match.

So if we have:
Record #      goodOrBad                                 Record #   User Name
1                    good                                          1                moe
2                    bad                                            2                larry
3                    good                                          3                 curly
                                                                       4                 shemp

lozloz will return
moe, good
curly, good

whereas aolXFT will return
moe, good
curly, good
shemp, null
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.