1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Unions, Views and something is amiss!

Discussion in 'MySQL' started by sarahk, Aug 31, 2017.

  1. #1
    I've got a table, that hasn't been normalised and we're not in a position to fix it anytime soon. I have a requirement that will be much easier if I create a view of that table with a union and make it look normalised.
    SEMrush
    So I've got a couple of fiddles

    In this one I can run a query with the union and get the predicted response
    http://sqlfiddle.com/#!9/c4d829/1

    In this one, though, I get errors if I put in the full union code (below) but it runs just fine as a single query but not with the union - on the line that says "union" I have an "invalid character"
    http://sqlfiddle.com/#!9/077e9b

    UNION
    SELECT
      registrations.`id`
      , registrations.`membership_id`
      , registrations.`partner_id`
      , registrations.`content_id`
      , registrations.`fees`
      , registrations.`options`
      , RPAD(registrations.`partnerattended`,3,' ') AS `attended`
    FROM
      `registrations`
    WHERE registrations.`softdelete` = 'N'
      AND registrations.`partner_id` IS NOT NULL
    Code (markup):
    Does anyone have any ideas what could be wrong?

    FWIW v5.5
     
    Last edited: Aug 31, 2017
    sarahk, Aug 31, 2017 IP
    SEMrush
  2. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #2
    Could you put up an SQL fiddle with the problematic code exactly as you have it now?
     
    PoPSiCLe, Aug 31, 2017 IP
  3. sarahk

    sarahk iTamer Staff

    Messages:
    23,835
    Likes Received:
    3,216
    Best Answers:
    91
    Trophy Points:
    615
    #3
    That's this one - http://sqlfiddle.com/#!9/077e9b
    but I can't save it with the extra sql in it.

    The entire schema should be:

    create table `registrations` 
    (`id` int(9) not null auto_increment,
     `membership_id` int(9),
     `individual_id` int(9),
     `partner_id` int(9) null,
     `content_id` int(9) null,
     `individualattended` char(3) null,
     `partnerattended` char(3) null,
     `fees` int(5),
     `options` text,
     `softdelete` char(1),
     primary key (`id`));
    
    insert into `registrations` values
     (1, 43, 2, 3, 4000, 'Yes', 'Yes', 50, '','N')
     , (2, 43, 2, null, 4001, 'Yes', '', 150, '','N')
     , (3, 44, 4, null, 4001, 'Yes', '', 250, '','N')
     , (4, 45, 5, null, 4001, 'Yes', '', 350, '','N')
     , (5, 46, 6, 9, 4001, 'Yes', 'No', 450, '','N')
     , (6, 47, 7, 8, 4001, 'Yes', 'No', 550, '','N');
                                 
    CREATE VIEW `vRegistrations` AS
    (SELECT
      registrations.`id`
      , registrations.`membership_id`
      , registrations.`individual_id`
      , registrations.`content_id`
      , registrations.`fees`
      , registrations.`options`
      , RPAD(registrations.`individualattended`,3,' ') AS `attended`
    FROM
      `registrations`
    WHERE registrations.`softdelete` = 'N'
      AND registrations.`individual_id` IS NOT NULL
     UNION
    SELECT
      registrations.`id`
      , registrations.`membership_id`
      , registrations.`partner_id`
      , registrations.`content_id`
      , registrations.`fees`
      , registrations.`options`
      , RPAD(registrations.`partnerattended`,3,' ') AS `attended`
    FROM
      `registrations`
    WHERE registrations.`softdelete` = 'N'
      AND registrations.`partner_id` IS NOT NULL
    );
    Code (markup):
     
    sarahk, Aug 31, 2017 IP
  4. sarahk

    sarahk iTamer Staff

    Messages:
    23,835
    Likes Received:
    3,216
    Best Answers:
    91
    Trophy Points:
    615
    #4
    I don't, seriously, don't believe this.

    So, without the union the syntax with parentheses around the select statement works
    
    CREATE VIEW `vRegistrations`
        AS
    ( SELECT ...);
    Code (markup):
    However, when you have a union you have to take the parentheses off!

    I'm sure that makes sense to somebody, somewhere but it looks crazy to me.

    ref: https://stackoverflow.com/questions...reate-view-that-contains-union#answer-9941656
     
    sarahk, Aug 31, 2017 IP
  5. PoPSiCLe

    PoPSiCLe Illustrious Member

    Messages:
    4,622
    Likes Received:
    717
    Best Answers:
    151
    Trophy Points:
    420
    #5
    That is... special... did NOT know that :)
     
    PoPSiCLe, Sep 2, 2017 IP
    sarahk likes this.