www.webdeveloper.com
Results 1 to 3 of 3

Thread: loop SQL!

  1. #1
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    loop SQL!

    I have seen many SQL Users searching for this form of SQL query which double searches one table using two or more primary crieteria. The minimal query form suits a collection of records in a table with a start,end and intermediate recorded steps. It can be modified to include an ID.

    select StepOne.Result as MainResult
    from
    (select
    if(locate('Condition1', column1) > 0, column2, null) as Result
    from
    TABLEname) as StepOne
    join TABLEname
    on
    TABLEname.Column3=StepOne.Result
    and StepOne.Result is not null
    and TABLEname.Column1="Condition Two"
    group by StepOne.Result desc

  2. #2
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234
    Here is/was a live http://www.wyc3.com example

    select
    p.f1
    as
    LibraryCard,count(wyc918_sys1.tagrequests1.cause)
    as
    frequency,
    wyc918_sys1.tagrequests1.cause
    from(
    (select cause,
    if(locate('Card', cause) > 0, name, null) as f1
    from
    wyc918_sys1.tagrequests1) as p
    join wyc918_sys1.tagrequests1
    on
    wyc918_sys1.tagrequests1.tag=p.f1
    and wyc918_sys1.tagrequests1.rdate >= DATE(NOW())
    and wyc918_sys1.tagrequests1.rtime >= TIME(NOW()-INTERVAL 30 min)
    and p.f1 is not null and
    wyc918_sys1.tagrequests1.cause="Access Point")
    group by p.f1 desc
    Outputs:
    LibraryCard============= frequency== cause
    20120305-202244-82821289====3====Access Point

  3. #3
    Join Date
    Feb 2012
    Location
    youTUBE
    Posts
    234

    sitepagehits

    Here is an easy one:
    My Site Front Page Hits.
    Code:
     
    select 
    hour(rtime)as 'GMT',
    count(hour(rtime)) as 'Hits-by-hour'
        from
            wyc918_sys1.tagrequests1
    where
     wyc918_sys1.tagrequests1.rdate >= DATE(NOW()) 
    and cause='Card'
    group  by rdate,hour(rtime)
    desc
    Outputs:
    GMT Hits-by-hour
    23 3
    22 1
    20 1
    19 1
    17 1
    14 1
    13 1
    12 1
    9 1
    0 1

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles