Professional Web Applications Themes

INNER JOIN query question - MySQL

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 ...

  1. #1

    Default INNER JOIN query question

    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.



    Bosconian Guest

  2. #2

    Default Re: INNER JOIN query question

    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
    Nicholas Sherlock Guest

  3. #3

    Default Re: INNER JOIN query question

    "Nicholas Sherlock" <N.sherlock> wrote in message
    news:dsn1a4$si9$3lust.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?







    Bosconian Guest

  4. #4

    Default Re: INNER JOIN query question

    > 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


    Vincent Lascaux Guest

  5. #5

    Default Re: INNER JOIN query question

    "Vincent Lascaux" <nospamnospam.org> wrote in message
    news:43ef83a2$0$311$626a14cenews.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.)


    Bosconian Guest

  6. #6

    Default Re: INNER JOIN query question

    >> > 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


    Vincent Lascaux Guest

  7. #7

    Default Re: INNER JOIN query question

    "Vincent Lascaux" <nospamnospam.org> wrote in message
    news:43ef9007$0$304$626a14cenews.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.


    Bosconian Guest

Similar Threads

  1. Inner Join Query
    By KimMazz in forum Coldfusion Database Access
    Replies: 2
    Last Post: June 13th, 05:19 PM
  2. question about SQL query and cfoutput with INNER JOIN
    By nedflanders in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 1st, 09:03 PM
  3. Query, view join question.
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 8
    Last Post: January 6th, 09:19 PM
  4. Query, view join question.
    By Joost Kraaijeveld in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 6th, 06:09 PM
  5. SQL join query help
    By poff in forum PHP Development
    Replies: 2
    Last Post: July 13th, 02:36 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139