INNER JOIN query question

[Bosconian]

I have 2 tables with the following columns (other data fields omitted for
brevity):

cases.caseid
cases.uid

profiles.uid
profiles.locationid

I need to return a list of caseid's based on a uid's locationid.

Before executing the query, I only know the uid. I could retrieve the uid's
locationid with a separate query, which would then make getting a list of
caseid's easy.

Example:

SELECT p.locationid
FROM profile p
WHERE p.uid = 1

SELECT c.caseid
FROM cases c
INNER JOIN profile p ON p.uid = c.uid
WHERE p.locationid = 1

However, for learning and efficiency purposes is there a way to do this with
one query?

Thanks.

[Nicholas Sherlock]

Bosconian wrote:

> SELECT p.locationid
> FROM profile p
> WHERE p.uid = 1
>
> SELECT c.caseid
> FROM cases c
> INNER JOIN profile p ON p.uid = c.uid
> WHERE p.locationid = 1
>
> However, for learning and efficiency purposes is there a way to do this with
> one query?

Do you mean this?

SELECT p.locationid, c.caseid
FROM profile p
INNER JOIN cases c ON p.uid=c.uid
WHERE p.uid = 1

Cheers,
Nicholas Sherlock

[Bosconian]

"Nicholas Sherlock" <N.sherlock@gmail.com> wrote in message
news:dsn1a4$si9$3@lust.ihug.co.nz...

> Bosconian wrote:

> > SELECT p.locationid
> > FROM profile p
> > WHERE p.uid = 1
> >
> > SELECT c.caseid
> > FROM cases c
> > INNER JOIN profile p ON p.uid = c.uid
> > WHERE p.locationid = 1
> >
> > However, for learning and efficiency purposes is there a way to do this

with

> > one query?

>
> Do you mean this?
>
> SELECT p.locationid, c.caseid
> FROM profile p
> INNER JOIN cases c ON p.uid=c.uid
> WHERE p.uid = 1
>
> Cheers,
> Nicholas Sherlock

Unfortunately, no.

Allow me to rephrase the question.

I need a list of caseid's for those uid's which match the current uid's
locationid.

So for example let's say the tables contain the following data:

case.caseid, case.uid
3, 1
5, 2
7, 3

profile.uid, profile.locationid
1 1
2 2
3 2

Assuming the current uid equals '2', the results would return caseid's 5 &
7. Make sense?

[Vincent Lascaux]

> So for example let's say the tables contain the following data:

>
> case.caseid, case.uid
> 3, 1
> 5, 2
> 7, 3
>
> profile.uid, profile.locationid
> 1 1
> 2 2
> 3 2
>
> Assuming the current uid equals '2', the results would return caseid's 5 &
> 7. Make sense?

SELECT case.caseid FROM Case
INNER JOIN Profile ON case.uid = profile.uid
WHERE profile.locationid = 2

Should do it I think

--
Vincent

[Bosconian]

"Vincent Lascaux" <nospam@nospam.org> wrote in message
news:43ef83a2$0$311$626a14ce@news.free.fr...

> > So for example let's say the tables contain the following data:
> >
> > case.caseid, case.uid
> > 3, 1
> > 5, 2
> > 7, 3
> >
> > profile.uid, profile.locationid
> > 1 1
> > 2 2
> > 3 2
> >
> > Assuming the current uid equals '2', the results would return caseid's 5

&

> > 7. Make sense?

>
> SELECT case.caseid FROM Case
> INNER JOIN Profile ON case.uid = profile.uid
> WHERE profile.locationid = 2
>
> Should do it I think
>
> --
> Vincent
>
>

You're right, except I don't know the current uid's locationid beforehand
(unless its retreived in a separate query, which currently seems to be the
easy way to go about this.)

[Vincent Lascaux]

>> > case.caseid, case.uid

>> > 3, 1
>> > 5, 2
>> > 7, 3
>> >
>> > profile.uid, profile.locationid
>> > 1 1
>> > 2 2
>> > 3 2
>> >
>> > Assuming the current uid equals '2', the results would return caseid's
>> > 5

> &

>> > 7. Make sense?

>
> You're right, except I don't know the current uid's locationid beforehand
> (unless its retreived in a separate query, which currently seems to be the
> easy way to go about this.)

I don't get it... What is the '2' in your example. If you don't know the
uid, how can you select the row that match that uid?

--
Vincent

[Bosconian]

"Vincent Lascaux" <nospam@nospam.org> wrote in message
news:43ef9007$0$304$626a14ce@news.free.fr...

> >> > case.caseid, case.uid
> >> > 3, 1
> >> > 5, 2
> >> > 7, 3
> >> >
> >> > profile.uid, profile.locationid
> >> > 1 1
> >> > 2 2
> >> > 3 2
> >> >
> >> > Assuming the current uid equals '2', the results would return

caseid's

> >> > 5

> > &

> >> > 7. Make sense?

> >
> > You're right, except I don't know the current uid's locationid

beforehand

> > (unless its retreived in a separate query, which currently seems to be

the

> > easy way to go about this.)

>
> I don't get it... What is the '2' in your example. If you don't know the
> uid, how can you select the row that match that uid?
>
> --
> Vincent
>
>

The data shown was for demonstration purposes only.

In any event I opted to retrieve the locationid before, which makes my life
MUCH easier.