Solved

Make Another Query Using the existing result set.

Posted on 2003-12-10
9
252 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …

910 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

16 Experts available now in Live!

Get 1:1 Help Now