List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:February 27 2006 8:07pm
Subject:Re: Connecting to queries into one
View as plain text  
Courtney,

 >SELECT consumers.id FROM consumers
 >WHERE date_of_birth = ? AND consumer.id =
 >(SELECT consumer_id FROM cases WHERE last_name = ?            
 >AND full_first_name = ? )

 >Is there a way to form the above statement in MYSQL?

Something like this would be faster ...

SELECT co.id
FROM cases AS ca
INNER JOIN consumers AS co USING (consumer_id)
WHERE co.date_of_birth = <given_value>
  AND ca.last_name = <given_value>
  AND ca.first_name = <given_value>;
 
PB


Courtney Braafhart wrote:
> MY GOAL
>        
>         To collect any consumers.id WHERE date of birth, last name and 
> first name matches  what was entered by the user.  The trick is that 
> date of birth lives in         the consumer table and last name and 
> first name lives in the cases table (which can be joined to consumers 
> by consumers.id and cases.consumer_id).
>
>         Can anyone think of way to do this in one mysql statement 
> instead of doing a loop?
>
>         I am thinking it would work something like this:
>
>         SELECT consumers.id FROM consumers WHERE date_of_birth = ? AND 
> consumer.id = (SELECT consumer_id FROM cases WHERE last_name = 
> ?             AND full_first_name = ? )
>
>         Is there a way to form the above statement in MYSQL?
>
> MY HOPE
>
>         That there is a really obvious solution to this question and 
> that I am simply suffering from a case of the Mondays!
>
>
> Thanks in advance!
>
> Courtney Braafhart
>
>
> --MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    
> http://lists.mysql.com/mysql?unsub=1
>
>
>
> --No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 2/24/2006

Thread
Connecting to queries into oneCourtney Braafhart27 Feb
  • RE: Connecting to queries into oneTaiyo27 Feb
  • Re: Connecting to queries into onePeter Brawley27 Feb
RE: Connecting to queries into oneJohn McCaskey27 Feb