Professional Web Applications Themes

left outer join problem - MySQL

Greetings fellow MySqlers, We are a non-profit, not-kill cat rescue society having some problems with our online reports. We need a report showing all volunteers (who foster cats) and any cats (if any) that they are presently fostering. We have tried many combinations of MySql queries and are beaten on all of them Our we site is hosted by a major Telecom using MySql 3.23 What we want is something like this: Select volunteers.*,cats.* from (select volunteers.* from volunteers, volunteersbygroup where volunteers.id = volunteersbygroup.vol_id and volunteersbygroup.groupid = '6' <- this selects only those volunteers whose duties are 'foster' ) left ...

  1. #1

    Default left outer join problem

    Greetings fellow MySqlers,

    We are a non-profit, not-kill cat rescue society having some problems
    with our online reports.

    We need a report showing all volunteers (who foster cats) and any cats
    (if any) that they are presently fostering.

    We have tried many combinations of MySql queries and are beaten on all
    of them

    Our we site is hosted by a major Telecom using MySql 3.23

    What we want is something like this:

    Select volunteers.*,cats.*
    from (select volunteers.*
    from volunteers, volunteersbygroup
    where volunteers.id = volunteersbygroup.vol_id and
    volunteersbygroup.groupid = '6' <- this selects only
    those volunteers whose duties are 'foster'
    )
    left outer join (select cats.*, foster.vol_id as VOL_ID
    from cats, foster
    where cats.id = foster.cat_id <- this selects
    only cats that are fostered
    )
    on volunteers.id = VOL_ID


    thanks for any help

    -garry

    softie Guest

  2. #2

    Default Re: left outer join problem

    softie wrote:
    > Greetings fellow MySqlers,
    >
    > We are a non-profit, not-kill cat rescue society having some problems
    > with our online reports.
    Hooray for cat rescuers! My cat is a rescued cat who lost her mother.
    She's now 8 years old, healthy and happy.
    > We need a report showing all volunteers (who foster cats) and any cats
    > (if any) that they are presently fostering.
    Right; MySQL 3.23 does not support subqueries, so you need a solution
    that uses only joins.

    Here's a try at it (note I have not tested this, but it should get you
    started):

    SELECT v.*, c.*
    FROM volunteers AS v
    INNER JOIN volunteersbygroup AS g
    ON v.id = g.vol_id AND g.groupid = 6
    LEFT OUTER JOIN (cats AS c INNER JOIN foster AS f ON c.id = f.cat_id)
    ON v.id = f.vol_id;

    I like to use JOIN syntax consistently, instead of comma-style joins.
    It's more clear that way. I also like to use correlation names (the
    "AS" things).

    Regards,
    Bill K.
    Bill Karwin Guest

  3. #3

    Default Re: left outer join problem

    Thanks Bill,
    But there is a syntax error that I can't figure out:

    [MyODBC]You have an error in your SQL syntax near '(Cats as c INNER
    JOIN Fosters AS f ON c.id = f.Cat_ID) ON v.ID = f.Foster_ID'


    Any more help is gratefully accepted.
    t.i.a.

    garry

    Bill Karwin wrote:
    > softie wrote:
    > > Greetings fellow MySqlers,
    > >
    > > We are a non-profit, not-kill cat rescue society having some problems
    > > with our online reports.
    >
    > Hooray for cat rescuers! My cat is a rescued cat who lost her mother.
    > She's now 8 years old, healthy and happy.
    >
    > > We need a report showing all volunteers (who foster cats) and any cats
    > > (if any) that they are presently fostering.
    >
    > Right; MySQL 3.23 does not support subqueries, so you need a solution
    > that uses only joins.
    >
    > Here's a try at it (note I have not tested this, but it should get you
    > started):
    >
    > SELECT v.*, c.*
    > FROM volunteers AS v
    > INNER JOIN volunteersbygroup AS g
    > ON v.id = g.vol_id AND g.groupid = 6
    > LEFT OUTER JOIN (cats AS c INNER JOIN foster AS f ON c.id = f.cat_id)
    > ON v.id = f.vol_id;
    >
    > I like to use JOIN syntax consistently, instead of comma-style joins.
    > It's more clear that way. I also like to use correlation names (the
    > "AS" things).
    >
    > Regards,
    > Bill K.
    softie Guest

  4. #4

    Default Re: left outer join problem

    softie wrote:
    > Thanks Bill,
    > But there is a syntax error that I can't figure out:
    >
    > [MyODBC]You have an error in your SQL syntax near '(Cats as c INNER
    > JOIN Fosters AS f ON c.id = f.Cat_ID) ON v.ID = f.Foster_ID'
    Well, that's a problem. Yes, the query I gave you works fine on MySQL
    5.0 which is what I use normally. So I tried downloading and installing
    MySQL 3.23.58, and the query fails with the message you describe.
    Apparently that older version of MySQL doesn't like parenthesized groups
    of tables in joins in the FROM clause.

    Is there any possibility that you could upgrade the version of MySQL
    you're using? MySQL 3.23 is terribly old at this point. I tried the
    query with MySQL 4.0 as well, and it works fine.

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default re:Left outer join problem

    Thanks again for your help.

    We originally used MS Access as the db but after multiple lockout
    problems, Telus
    forced (well, thats too strong, but almost forced) us to convert to
    MySql.

    I did talk to our isp (TELUS) about the antique version of mysql.

    I started the conversation with "i guess that we will have to convert
    back to Access".

    On Sept 9 06, they will update to version 4.023

    I can hardly wait for the big day.

    Here's our cat rescue site:
    [url]http://www.meowfoundation.com[/url]


    thanks again
    softie

    softie Guest

  6. #6

    Default Re: Left outer join problem

    softie wrote:
    > On Sept 9 06, they will update to version 4.023
    That's still pretty disappointing. 4.0.23 is old (December 2004).
    There are more recent releases of 4.0.x, and of course 4.1 and 5.0.
    There are important bugs fixed in 4.0.24 through 4.0.27.

    They should install 4.1.21 and run it with "old-passwords" so that all
    the existing MySQL accounts continue to work. There's no excuse to be
    upgrading to an outdated version of MySQL.

    See [url]http://dev.mysql.com/doc/refman/4.1/en/news.html[/url] for a list of
    releases and their dates.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. Outer join?
    By Ignoramus23298 in forum MySQL
    Replies: 5
    Last Post: May 22nd, 09:06 AM
  2. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  3. OUTER JOIN
    By jorgepino in forum Coldfusion - Advanced Techniques
    Replies: 1
    Last Post: May 23rd, 12:59 PM
  4. SQL query with a Left outer Join
    By Ad Bec in forum Coldfusion Database Access
    Replies: 2
    Last Post: April 22nd, 04:48 AM
  5. left join problem
    By AK in forum IBM DB2
    Replies: 8
    Last Post: August 8th, 12:16 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