MySQL ODBC Connector - Connecting to queries into one

Author

Connecting to queries into one

Courtney Braafhart

2006-02-28, 8:28 pm

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...sie.nctu.edu.tw

Taiyo

2006-02-28, 8:28 pm

select consumers.id from consumers LEFT JOIN cases ON consumers.id =
cases.id WHERE consumers.date_of_birth = '?' AND cases.last_name = '?' AND
cases.full_first_name = '?'

-----Original Message-----
From: Courtney Braafhart [mailto:courtney.braafhart@vr.ne.gov]
Sent: Monday, February 27, 2006 1:31 PM
To: mysql@lists.mysql.com
Subject: Connecting to queries into one

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...ric
e.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

John McCaskey

2006-02-28, 8:28 pm

SELECT consumers.id FROM consumers, cases=20
WHERE=20
consumers.id=3Dcases.consumers_id=20
AND consumers.date_of_birth =3D ?=20
AND cases.last_name =3D ?
AND cases.first_name =3D ?

John A. McCaskey

-----Original Message-----
From: Courtney Braafhart [mailto:courtney.braafhart@vr.ne.gov]=20
Sent: Monday, February 27, 2006 10:31 AM
To: mysql@lists.mysql.com
Subject: Connecting to queries into one

MY GOAL
=09
To collect any consumers.id WHERE date of birth, last
name and =20
first name matches what was entered by the user. The trick is that =20
date of birth lives in the consumer table and last name and
first =20
name lives in the cases table (which can be joined to consumers by =20
consumers.id and cases.consumer_id).

Can anyone think of way to do this in one mysql
statement instead =20
of doing a loop?

I am thinking it would work something like this:

SELECT consumers.id FROM consumers WHERE date_of_birth =3D
? AND =20
consumer.id =3D (SELECT consumer_id FROM cases WHERE last_name =3D ?

AND full_first_name =3D ? )

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 =20
am simply suffering from a case of the Mondays!

Thanks in advance!

Courtney Braafhart

--=20
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql? unsub...
nces.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Peter Brawley

2006-02-28, 8:28 pm

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=...y@earthlink.net
>
>
>
> --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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links