GROUP BY idguest [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <127a07to0kpticf@corp.supernews.com> [ref] => [htmlstate] => on_nl2br [postusername] => Jared [ip] => jared@acb.com [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 1 [islastshown] => [isfirstshown] => 1 [attachments] => [allattachments] => ) --> > > GROUP BY idguest[/quote] SELECT idguest, SUM(Expr1) AS TA, SUM(Expr2) AS TF, SUM(Expr3) AS TM FROM your_view_name What is the problem you're experiencing? You can use your view as any table. -- Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions [url]http://www.upscene.com[/url] My thoughts: [url]http://blog.upscene.com/martijn/[/url] Database development questions? Check the forum! [url]http://www.databasedevelopmentforum.com[/url] [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => <127aqb3gn2m6h99@corp.supernews.com> [ref] => <127a07to0kpticf@corp.supernews.com> [htmlstate] => on_nl2br [postusername] => Martijn Tonies [ip] => m.tonies@upscen [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 3 [islastshown] => 1 [isfirstshown] => [attachments] => [allattachments] => ) --> Combine veiw with query - MySQL
Professional Web Applications Themes

Combine veiw with query - MySQL

Hi, I have a View which I woulk like to combine with a query VIEW --------- (SELECT `idguest` AS `idguest`, SUM(price) AS Expr1, 0 AS Expr2, 0 AS Expr3 FROM `sale-rooms` WHERE (`idevent` = 2) GROUP BY idguest) UNION (SELECT `idguest` AS `idguest`, 0 AS Expr1, SUM(price) As Expr2, 0 AS Expr3 FROM `sale-meals` WHERE (`idevent` = 2) GROUP BY idguest) UNION (SELECT `idguest` AS `idguest`, 0 AS Expr1, 0 AS Expr2, SUM(price) AS Expr3 FROM `sale-misc` WHERE (`idevent` = 2) GROUP BY idguest) QUERY ---------- SELECT idguest, SUM(Expr1) AS TA, SUM(Expr2) AS TF, SUM(Expr3) AS TM FROM <---VIEW---> GROUP ...

  1. #1

    Default Combine veiw with query

    Hi,

    I have a View which I woulk like to combine with a query


    VIEW
    ---------
    (SELECT `idguest` AS `idguest`, SUM(price) AS Expr1, 0 AS Expr2, 0
    AS Expr3 FROM `sale-rooms` WHERE (`idevent` = 2) GROUP BY idguest)
    UNION
    (SELECT `idguest` AS `idguest`, 0 AS Expr1, SUM(price) As Expr2, 0
    AS Expr3 FROM `sale-meals` WHERE (`idevent` = 2) GROUP BY idguest)
    UNION
    (SELECT `idguest` AS `idguest`, 0 AS Expr1, 0 AS Expr2,
    SUM(price) AS Expr3 FROM `sale-misc` WHERE (`idevent` = 2) GROUP BY
    idguest)


    QUERY
    ----------
    SELECT idguest, SUM(Expr1) AS TA, SUM(Expr2) AS TF, SUM(Expr3) AS TM
    FROM

    <---VIEW--->

    GROUP BY idguest


    Jared Guest

  2. #2

    Default Re: Combine veiw with query

    The best I have come up with so far is to Insert results from one qeury into
    temp table and use that table in the second query.


    Jared Guest

  3. #3

    Default Re: Combine veiw with query

    > I have a View which I woulk like to combine with a query
    >
    >
    > VIEW
    > ---------
    > (SELECT `idguest` AS `idguest`, SUM(price) AS Expr1, 0 AS Expr2,
    0
    > AS Expr3 FROM `sale-rooms` WHERE (`idevent` = 2) GROUP BY
    idguest)
    > UNION
    > (SELECT `idguest` AS `idguest`, 0 AS Expr1, SUM(price) As Expr2,
    0
    > AS Expr3 FROM `sale-meals` WHERE (`idevent` = 2) GROUP BY
    idguest)
    > UNION
    > (SELECT `idguest` AS `idguest`, 0 AS Expr1, 0 AS Expr2,
    > SUM(price) AS Expr3 FROM `sale-misc` WHERE (`idevent` = 2) GROUP BY
    > idguest)
    >
    >
    > QUERY
    > ----------
    > SELECT idguest, SUM(Expr1) AS TA, SUM(Expr2) AS TF, SUM(Expr3) AS TM
    > FROM
    >
    > <---VIEW--->
    >
    > GROUP BY idguest
    SELECT idguest, SUM(Expr1) AS TA, SUM(Expr2) AS TF, SUM(Expr3) AS TM
    FROM your_view_name


    What is the problem you're experiencing?

    You can use your view as any table.


    --
    Martijn Tonies
    Database Workbench - development tool for MySQL, and more!
    Upscene Productions
    [url]http://www.upscene.com[/url]
    My thoughts:
    [url]http://blog.upscene.com/martijn/[/url]
    Database development questions? Check the forum!
    [url]http://www.databasedevelopmentforum.com[/url]


    Martijn Tonies Guest

Similar Threads

  1. If I combine two PDFs...
    By Doug_Katz@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 23
    Last Post: December 16th, 06:26 PM
  2. How to combine rectangles?
    By Malte_Persike@adobeforums.com in forum Adobe Illustrator Windows
    Replies: 5
    Last Post: August 3rd, 07:43 PM
  3. is it possible to combine jsp and php?
    By Blah Blah in forum PHP Development
    Replies: 7
    Last Post: November 19th, 11:17 PM
  4. To combine or leave as is...
    By David P. Jessup in forum ASP
    Replies: 11
    Last Post: October 17th, 07:25 PM
  5. unable to veiw background images
    By Kez Bates in forum ASP
    Replies: 4
    Last Post: July 11th, 01:59 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