SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Thread: Ugly Queries

  1. #1
    I solve practical problems. bronze trophy
    Michael Morris's Avatar
    Join Date
    Jan 2008
    Location
    Knoxville TN
    Posts
    2,015
    Mentioned
    62 Post(s)
    Tagged
    0 Thread(s)

    Ugly Queries

    A thread for ugly queries. Not problem non-working queries mind you - just ugly or overly complicated queries

    Just finished writing this little monster

    Code:
    SELECT fac.old AS o, md.new AS n
    FROM import AS fac
    	JOIN facilities ON facilities.recordid = fac.new AND fac.tble = 'facilities'
    	JOIN providers ON providers.facilityid = facilities.recordid
    	JOIN import AS md ON md.new = providers.recordid AND md.tble = 'providers'
    Ok, here's what's going on - The 'import' table is a temporary index of keys from a new database against the old keys from the database I'm importing from. In order to preserve relationships I have to translate the keys at several points of the importer script.

    (Now yes, I could put an old id field on each of the import tables, but since that data isn't going to be used post import I'd rather keep it on an import table that I can throw away later rather than remembering to drop the old id field off some 20 tables).

    In this particular query I have a situation where I'm preparing to map medical procedure codes to the doctors that use them. Unfortunately the old system maps the codes to the facility the doctor works for. This was an error on the prior programmer's part - each doctor needs their own procedure list (or more accurately, procedure fee list). Fortunately the old system has a 1:1 relationship of doctors to facilities, but we won't have this moving forward.

    So my import table needs to be joined to ITSELF through the provider and facility tables in order to call back an associated array of old facility id to new provider id that I'll be inserting during import.

    It works, but it's ugly.

  2. #2
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,251
    Mentioned
    113 Post(s)
    Tagged
    1 Thread(s)
    Joining tables to itself is not that entirely off the wall and is done more than you think.

    If only you knew what truly ugly was.....I'd show you, but I'm just now getting over the nightmares
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style

  3. #3
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    This is a single SELECT query in one of my websites:

    PHP Code:
    $sql "
    SELECT SQL_CALC_FOUND_ROWS 
      landing_page.id AS `landing_page_id`,
      landing_page.name AS `landing_page_name`,
      landing_page.url,
      landing_page.created_at,
      landing_page.updated_at,
      landing_page.deleted,
      ad.id AS `ad_id`,
      ad.name AS `ad_name`,    
      ad_group.id AS `ad_group_id`,
      ad_group.name AS `ad_group_name`,
      campaign.id AS `campaign_id`,
      campaign.name AS `campaign_name`,              
      COUNT(click.id) AS `clicks`,
      (SELECT
            COUNT(*) AS `clicks`
        FROM
            click
        INNER JOIN
            landing_page
                ON click.landing_page_id = landing_page.id
        WHERE
            landing_page.ad_id = MAX(ad.id) 
            AND click.created_at >= '" 
    $args['ds'] . "' AND click.created_at <= '" $args['de'] . "' 
            AND click.offer_click = 1
      ) AS `offer_clicks`,
      COALESCE(SUM(click.cost),0) AS `cost`,
      COALESCE(cost_table.cost,0) AS `flat`,
      COALESCE(action_table.`count`,0) AS `actions`,
      COALESCE(action_table.value, 0) AS `revenue`,
      COALESCE(action_table.value, 0) - COALESCE(SUM(click.cost),0) AS `profit`,
      COALESCE(COALESCE(action_table.`count`,0) / COUNT(click.id), 0) * 100 AS `convrate`,
      COALESCE(COALESCE(SUM(click.cost),0) / COALESCE(action_table.`count`,0), 0) AS `costconv`,
      COALESCE(
          (COALESCE(action_table.value, 0) - COALESCE(SUM(click.cost),0))
          /
          COALESCE(SUM(click.cost),0)
      ,0) * 100 AS `roi`"
    ;
      
    if (isset(
    $args['ds']) && isset($args['de'])) {
        
    $sql .= ", DATE(CONVERT_TZ(click.created_at,'GMT','$timezone')) AS `date`";
    }  
      
    $sql .= "          
    FROM
      landing_page
    INNER JOIN
      ad
        ON landing_page.ad_id = ad.id "
    ;
     
    if (isset(
    $args['ad_id']))
        
    $sql .= " AND ad.id = " $args['ad_id'];

    $sql .= "            
    INNER JOIN
      ad_group
        ON ad.ad_group_id = ad_group.id "
    ;
     
    if (isset(
    $args['ad_group_id'])) 
        
    $sql .= " AND ad_group.id = " $args['ad_group_id'];

    $sql .= "            
    INNER JOIN
      campaign
        ON ad_group.campaign_id = campaign.id "
    ;
     
    if (isset(
    $args['campaign_id'])) 
        
    $sql .= " AND ad_group.campaign_id = " $args['campaign_id'];

    if (!empty(
    $args['restrict_to']))
        
    $sql .= " AND campaign.id = " $args['restrict_to'] . " ";

    $sql .= "
    LEFT OUTER JOIN
      click
        ON click.landing_page_id = landing_page.id 
            AND click.created_at >= '" 
    $args['ds'] . "' AND click.created_at <= '" $args['de'] . "' 
            AND click.offer_click = 0
            AND click.deleted = 0
    LEFT OUTER JOIN
        (
            SELECT 
                landing_page.id AS `landing_page_id`,
                  COUNT(action.id) AS `count`,
                  COALESCE(SUM(action.custom_amount),0) AS `value`,
                  DATE(CONVERT_TZ(action.created_at,'GMT','
    $timezone')) AS `date` 
              FROM
                  action
              INNER JOIN
                  click
                      ON click.id = action.click_id
              INNER JOIN
                  landing_page
                      ON landing_page.id = click.landing_page_id
              INNER JOIN
                  ad
                      ON ad.id = landing_page.ad_id
              INNER JOIN
                  ad_group
                      ON ad_group.id = ad.ad_group_id
              INNER JOIN
                  campaign
                      ON campaign.id = ad_group.campaign_id
              "
    ;
              if (isset(
    $args['ad_id'])) $sql .= " AND ad.id = " $args['ad_id'];
              if (isset(
    $args['ad_group_id'])) $sql .= " AND ad_group.id = " $args['ad_group_id'];
              if (isset(
    $args['campaign_id'])) $sql .= " AND campaign.id = " $args['campaign_id'];
            if (!empty(
    $args['restrict_to'])) $sql .= " AND campaign.id = " $args['restrict_to'];
              
              
    $sql .= "
              WHERE
                  action.deleted = 0
                    AND action.created_at >= '" 
    $args['ds'] . "' AND action.created_at <= '" $args['de'] . "' 
            "
    ;
            if (
    count($args['selected']) > 0) {
                
    $sql .= "AND landing_page.id IN (" implode(', '$args['selected']) . ") ";
            }                 
            
            
    $sql .= " GROUP BY ";
                    
            if (isset(
    $args['graph'])) {
                
    $sql .= "DATE(CONVERT_TZ(action.created_at,'GMT','$timezone')) ";
            } else {
                
    $sql .= "landing_page.id";
            }
            
            
    $sql .= "
          ) AS `action_table` 
          "
    ;
          
          if (isset(
    $args['graph'])) {
              
    $sql .= "ON action_table.`date` = DATE(CONVERT_TZ(click.created_at,'GMT','$timezone'))";
          } else {
              
    $sql .= "ON action_table.landing_page_id = landing_page.id";
          }

    $sql .= "
    LEFT OUTER JOIN
        (
            SELECT
                ad_group.id AS `ad_group_id`,
                COALESCE(
                        CASE 
                            WHEN ad_group.cost_type = 'monthly' 
                            THEN ad_group.cost * 
    $num_months 
                            ELSE 
                                CASE WHEN
                                    ad_group.cost_type = 'flat' 
                                    AND ad_group.created_at >= '
    {$args['ds']}
                                    AND ad_group.created_at <= '
    {$args['de']}
                                THEN
                                    ad_group.cost
                                ELSE
                                    0
                                END
                        END
                ,0) AS `cost`
            FROM
                ad_group
            WHERE
                ad_group.deleted = 0
                    AND (ad_group.cost_type = 'monthly' OR ad_group.cost_type = 'flat')
        ) AS `cost_table`
        ON cost_table.ad_group_id = ad_group.id
    WHERE
      landing_page.deleted = 0 "


  4. #4
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I wish the ones i have to work with look like that... currently looking at one that fills in 3 screens and goes over 3 databases (yes, databases...).

  5. #5
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any software/editor that people find helpful when working with huge sql queries? For example, I was thinking the ability to visually collapse/expand a subquery, and maybe annotate it. Kinda like how a code editor lets you collapse a nested block of code.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i use UltraEdit, which is a "general purpose" code editor

    it supports various languages like hmtl, css, sql, and of course all the various programming languages like c, vb, java, perl...

    all the sql i've ever written over the past nine years or so has been written in ultraedit
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •