Professional Web Applications Themes

Intersection of Two Queries - MySQL

I want to find users who have more than x pages views yesterday and more than y page views during the last 30 days. My two queries work well independently. But how do I find users that satisfy both conditions? SELECT DISTINCT user_id FROM `pageviews` WHERE pagevisit LIKE '2007-03-23%' GROUP BY user_id HAVING count( id ) > x SELECT user_id FROM pageviews WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit GROUP BY user_id HAVING count(DISTINCT(date(pagevisit))) > y The UNION of both these queries also works fine, but that would give me users that satisfy either condition, right? I only want users ...

  1. #1

    Default Intersection of Two Queries

    I want to find users who have more than x pages views yesterday and
    more than y page views during the last 30 days. My two queries work
    well independently. But how do I find users that satisfy both
    conditions?

    SELECT DISTINCT user_id
    FROM `pageviews`
    WHERE pagevisit LIKE '2007-03-23%'
    GROUP BY user_id
    HAVING count( id ) > x

    SELECT user_id
    FROM pageviews
    WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
    GROUP BY user_id
    HAVING count(DISTINCT(date(pagevisit))) > y

    The UNION of both these queries also works fine, but that would give
    me users that satisfy either condition, right? I only want users that
    satisfy *both* conditions.

    Any help would be greatly appreciated.

    Cheers,
    Krishna Srinivasan.

    cookieplanter Guest

  2. #2

    Default Re: Intersection of Two Queries

    cookieplanter wrote: 

    Well you could really do with INTERSECT, but of course MySQL doen't support
    it yet.

    So you could make one of them a subquery and do a FULL OUTER JOIN, rejecting
    all records where either side of the join is null.


    Paul Guest

  3. #3

    Default Re: Intersection of Two Queries

    Paul,

    Untested, but try this:

    SELECT pv1.userid FROM
    (
    SELECT DISTINCT user_id
    FROM `pageviews`
    WHERE pagevisit LIKE '2007-03-23%'
    GROUP BY user_id
    HAVING count( id ) > x
    ) pv1
    INNER JOIN
    (
    SELECT user_id
    FROM pageviews
    WHERE DATE_SUB(CURDATE(), INTERVAL 90 DAY) <= pagevisit
    GROUP BY user_id
    HAVING count(DISTINCT(date(pagevisit))) > y
    ) pv2
    on pv1.user_id = pv2.user_id

    -- Bill

    "Paul Lautman" <com> wrote in message
    news:individual.net... 
    >
    > Well you could really do with INTERSECT, but of course MySQL doen't
    > support it yet.
    >
    > So you could make one of them a subquery and do a FULL OUTER JOIN,
    > rejecting all records where either side of the join is null.
    >[/ref]


    AlterEgo Guest

  4. #4

    Default Re: Intersection of Two Queries

    On Mar 24, 1:36 am, "AlterEgo" <com> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]

    > [/ref]

    This works! Thank you so much.

    Krishna Srinivasan.

    cookieplanter Guest

Similar Threads

  1. Intersection of two lists
    By ggle75@carpelibris.com in forum MySQL
    Replies: 12
    Last Post: November 23rd, 12:34 PM
  2. Queries Of Queries Single Quote Problem
    By TimH2O in forum Macromedia ColdFusion
    Replies: 0
    Last Post: April 1st, 07:46 PM
  3. FH: intersection points
    By Kaffee365 webforumsuser@macromedia.com in forum Macromedia Freehand
    Replies: 2
    Last Post: January 3rd, 10:41 PM
  4. Model within model transform.position, intersection, overlapping models
    By Zafada webforumsuser@macromedia.com in forum Macromedia Director 3D
    Replies: 0
    Last Post: August 30th, 12:30 AM

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