Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Make Another Query Using the existing result set.

Posted on 2003-12-10
9
256 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
parse url to form? 7 25
PHP and MSSQL Arrays and Variables 3 23
Force PDF to open inline as opposed to dowload 16 22
PHP 5.6 and 7.x 4 20
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
This article discusses four methods for overlaying images in a container on a web page
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
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 …

808 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