Solved

Make Another Query Using the existing result set.

Posted on 2003-12-10
9
251 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
 
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now