Professional Web Applications Themes

Trying To Avoid Two Database Calls... - MySQL

Hi there I am trying to get some information from a table, and I think I might be asking too much in one query. I need to get the maximum PlanNum (easy), but also the Alias that goes along with that PlanNum (can't seem to do it). Here's the table: FloorID | BuildingID | Stack | Alias | PlanNum ---------------------------------------------- 307 30 1 Floor 1 1 308 30 2 Floor 2 1 309 30 3 Floor 3 1 310 30 -1 Floor -1 1 493 30 -1 Floor -1 2 494 30 -1 Basement 3 My current query is: SELECT ...

  1. #1

    Default Trying To Avoid Two Database Calls...

    Hi there

    I am trying to get some information from a table, and I think I might be
    asking too much in one query. I need to get the maximum PlanNum (easy), but
    also the Alias that goes along with that PlanNum (can't seem to do it).

    Here's the table:

    FloorID | BuildingID | Stack | Alias | PlanNum
    ----------------------------------------------
    307 30 1 Floor 1 1
    308 30 2 Floor 2 1
    309 30 3 Floor 3 1
    310 30 -1 Floor -1 1
    493 30 -1 Floor -1 2
    494 30 -1 Basement 3


    My current query is:
    SELECT FloorID,Stack,MAX(PlanNum) as PlanNum,Alias FROM pm_Floors WHERE
    BuildingID=30 GROUP BY Stack ORDER BY Stack

    That gives me:
    310 / -1 / 3 / Floor -1
    307 / 1 / 1 / Floor 1
    308 / 2 / 1 / Floor 2
    309 / 3 / 1 / Floor 3

    I'm looking for that first result (stack -1) to give me the alias of
    "Basement" (it's latest alias) instead of "Floor -1"....

    Possible with one call?

    Thanks!
    Good Guest

  2. #2

    Default Re: Trying To Avoid Two Database Calls...


    Good Man wrote:
     

    Strawberry answered a simiilar question to this over on alt.php.sql

    The following query gives almost what you asked for, the only diference
    being that the output of FloorID agrees with the final alias entry. Is
    this a problem?

    SELECT pf1.FloorID, pf1.Stack, MAX( pf1.PlanNum ) AS PlanNum, pf1.Alias
    FROM pm_Floors pf1
    LEFT JOIN pm_Floors pf2 ON pf1.Stack = pf2.Stack
    AND pf1.FloorID < pf2.FloorID
    WHERE pf2.FloorID IS NULL
    AND pf1.BuildingID =30
    GROUP BY Stack
    ORDER BY Stack

    Which gives:
    494/-1/3/Basement
    307/1/1/Floor 1
    308/2/1/Floor 2
    309/3/1/Floor 3

    Captain Guest

  3. #3

    Default Re: Trying To Avoid Two Database Calls...

    "Captain Paralytic" <com> wrote in
    news:googlegroups.com:
     

    That would actually be a most excellent result, however in duplicating
    the query I'm finding it ONLY returns the first row there
    (494/-1/3/Basement)

    I will play around and see if I can come up with the desired result.
    Time to read about self-joins I guess.

    Thanks for the tip.

    Good Guest

  4. #4

    Default Re: Trying To Avoid Two Database Calls...

    Good Man wrote: 
    >
    > That would actually be a most excellent result, however in duplicating
    > the query I'm finding it ONLY returns the first row there
    > (494/-1/3/Basement)
    >
    > I will play around and see if I can come up with the desired result.
    > Time to read about self-joins I guess.
    >
    > Thanks for the tip.[/ref]

    Well those results that I posted were from running the query. I suspect that
    the query can be simplified by removing he MAX() and the GROUP BY clause.


    Paul Guest

  5. #5

    Default Re: Trying To Avoid Two Database Calls...

    Paul Lautman wrote: 
    >>
    >> That would actually be a most excellent result, however in
    >> duplicating the query I'm finding it ONLY returns the first row there
    >> (494/-1/3/Basement)
    >>
    >> I will play around and see if I can come up with the desired result.
    >> Time to read about self-joins I guess.
    >>
    >> Thanks for the tip.[/ref]
    >
    > Well those results that I posted were from running the query. I
    > suspect that the query can be simplified by removing he MAX() and the
    > GROUP BY clause.[/ref]

    I can't see why you should get only one row.

    The query can be simplified to:

    SELECT pf1. *
    FROM pm_Floors pf1
    LEFT JOIN pm_Floors pf2 ON pf1.Stack = pf2.Stack
    AND pf1.FloorID < pf2.FloorID
    WHERE pf2.FloorID IS NULL
    AND pf1.BuildingID =30
    ORDER BY Stack


    Paul Guest

  6. #6

    Default Re: Trying To Avoid Two Database Calls...

    "Paul Lautman" <com> wrote in
    news:net:
     [/ref]
    >
    > I can't see why you should get only one row.
    >
    > The query can be simplified to:
    >
    > SELECT pf1. *
    > FROM pm_Floors pf1
    > LEFT JOIN pm_Floors pf2 ON pf1.Stack = pf2.Stack
    > AND pf1.FloorID < pf2.FloorID
    > WHERE pf2.FloorID IS NULL
    > AND pf1.BuildingID =30
    > ORDER BY Stack[/ref]

    I still only get the one row returned. Looking at the "explain" behind the
    query, I can see that 6 rows are being returned from the first part (ref:
    const; Extra: Using where; Using filesort), which makes sense, and the 2nd
    part says it returns 3 row (ref: <dbname>.pf1.Stack; Extra: Using where;
    Using index; Not exists).

    I'm flummoxed. Thanks for your help.

    Again, the original table data for the BuildingID I am referencing is:

    FloorID | BuildingID | Stack | Alias | PlanNum
    ----------------------------------------------
    307 30 1 Floor 1 1
    308 30 2 Floor 2 1
    309 30 3 Floor 3 1
    310 30 -1 Floor -1 1
    493 30 -1 Floor -1 2
    494 30 -1 Basement 3


    Good Guest

  7. #7

    Default Re: Trying To Avoid Two Database Calls...

    Good Man <com> wrote in
    news:196.97.131:
     [/ref]

    I'm beginning to think that the query you so gratefully suggested is
    incorrect. With the following table data:

    FloorID | BuildingID | Stack | Alias | PlanNum
    ----------------------------------------------
    226 9 1 Mezzanine 1
    227 9 3 Level Eggs 1
    228 9 -1 Dingy Basement 1
    229 9 2 Floor 2 1
    230 9 2 Floor 2 2
    231 9 4 Fourth Floor 1
    539 9 3 Level Eggs 2
    554 9 4 Fourth Floor 2
    555 9 1 Mezzanine 2
    556 9 2 Level Bacon 3


    And the query subsequently changed to:
    SELECT pf1. *
    FROM pm_Floors pf1
    LEFT JOIN pm_Floors pf2 ON pf1.Stack = pf2.Stack
    AND pf1.FloorID < pf2.FloorID
    WHERE pf2.FloorID IS NULL
    AND pf1.BuildingID =9
    ORDER BY Stack

    I get the following, which is almost perfect, except that stack 3 is
    missing entirely (FloorID / Stack / PlanNum / Alias):

    555 / 1 / 2 / Mezzanine
    556 / 2 / 3 / Level Bacon
    554 / 4 / 2 / Fourth Floor

    So, stack 3 (Level Eggs) is missing completely....

    Thanks for your help, I will give up and go with two database calls
    shortly if I can't get my head around this.....


    Good Guest

  8. #8

    Default Re: Trying To Avoid Two Database Calls...

    Good Man <com> wrote in news:Xns9859B6A9A7BBFsonicyouth
    216.196.97.131:
     

    (as is stack -1)


    Good Guest

  9. #9

    Default Re: Trying To Avoid Two Database Calls...


    Good Man wrote: 
    >
    > (as is stack -1)[/ref]

    Try this one then:

    SELECT pf1. *
    FROM pm_Floors pf1
    LEFT JOIN pm_Floors pf2 ON pf1.Stack = pf2.Stack
    AND pf1.BuildingID = pf2.BuildingID
    AND pf1.FloorID < pf2.FloorID
    WHERE pf2.FloorID IS NULL
    AND pf1.BuildingID =9
    ORDER BY Stack

    Captain Guest

  10. #10

    Default Re: Trying To Avoid Two Database Calls...

    "Captain Paralytic" <com> wrote in
    news:googlegroups.com:
     

    Thanks very much. I owe you an internet beer.

    I've been writing (relatively simple) queries and joins for a couple of
    years, but this situation really had me stumped. I'm still stumped by
    the IS NULL part of your query combined with the pf1.FloorID <
    pf2.FloorID but i'll try go figure that one out on my own.

    Again, many thanks for your time and solution!

    Best,
    GM
    Good Guest

  11. #11

    Default Re: Trying To Avoid Two Database Calls...


    Good Man wrote:
     
    >
    > Thanks very much. I owe you an internet beer.
    >
    > I've been writing (relatively simple) queries and joins for a couple of
    > years, but this situation really had me stumped. I'm still stumped by
    > the IS NULL part of your query combined with the pf1.FloorID <
    > pf2.FloorID but i'll try go figure that one out on my own.
    >
    > Again, many thanks for your time and solution!
    >
    > Best,
    > GM[/ref]

    That bit is a real doosie to work out.

    We are doing a left join and specifying that the join must have the id
    from the second table being greater than the id from the first table as
    well as the stack ids being teh same. Now the only case where this is
    not true is where the record from the first table has the highest id.
    In that case there will be no match in the second table and so any
    second table fields will be null.

    Captain Guest

Similar Threads

  1. Two Function Calls to a Database to Retrieve the UniqueID of Inserted Records
    By rycbn in forum Coldfusion - Advanced Techniques
    Replies: 11
    Last Post: July 14th, 08:22 AM
  2. How can I avoid NaN
    By trints in forum Macromedia Flash Actionscript
    Replies: 5
    Last Post: February 27th, 03:15 PM
  3. How to avoid this
    By WWX webforumsuser@macromedia.com in forum Macromedia Director Lingo
    Replies: 4
    Last Post: December 9th, 05:33 PM
  4. Avoid Insert
    By Henry in forum Macromedia Dreamweaver
    Replies: 3
    Last Post: July 18th, 04:42 AM
  5. how to avoid authentication
    By Konrad in forum ASP.NET General
    Replies: 1
    Last Post: June 25th, 11:51 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