Solved

Make Another Query Using the existing result set.

Posted on 2003-12-10
9
255 Views
Last Modified: 2011-10-03
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)

0
Comment
Question by:rfr1tz
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 13

Expert Comment

by:lozloz
ID: 9912901
$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
 
LVL 3

Author Comment

by:rfr1tz
ID: 9913013
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
 
LVL 13

Expert Comment

by:lozloz
ID: 9913357
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 6

Expert Comment

by:aolXFT
ID: 9913461
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 9913711
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
 
LVL 3

Author Comment

by:rfr1tz
ID: 9913768
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
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 250 total points
ID: 9913798
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
 
LVL 13

Expert Comment

by:lozloz
ID: 9913839
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
 
LVL 33

Expert Comment

by:snoyes_jw
ID: 9914331
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

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question