SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    retrieving correct values in UNION

    Code:
    data in myTable "text_advertizement"
    (column name) value
    (m) 2
    (o) 101
    (top_s) white teeth
    (say_search)  keep your teeth white 
    (url) whiteplus.co.kr/KRMain/index/ViewLogic/index.php 
    
      
    data in myTable "banner_advertizement"
    
    (column name) value
    
    (s) 4
    (id) 0 
    (pw) 2
    (img) cyber.jungchul.com/img/topmenu/logo_slogan.gif 
    (link) cyber.jungchul.com/event/200511/toeic_emain.asp
    (width) 372 
    (height) 120
    I have above data in mytables.

    The following code produces the following result.
    Code:
    code
    
    (select s,id,pw,img,link,width,height
    from banner_advertizement)
    union
    (select m,o,top_s,say_search,url,null,null
    from text_advertizement)
    
    result
    
    2, 101, white te, keep your teeth white, whiteplus.co.kr/KRMain/index/ViewLogic/index.php, 0, 0
    
    4, 0, 2, cyber.jungchul.com/img/topmenu/logo_slogan.gif, cyber.jungchul.com/event/200511/toeic_emain.asp, 372, 120
    I like to produce the following target result.

    Code:
    target result
    
    2, 101, white teeth, keep your teeth white, whiteplus.co.kr/KRMain/index/ViewLogic/index.php, 0, 0
    
    4, 0, 2, cyber.jungchul.com/img/topmenu/logo_slogan.gif, cyber.jungchul.com/event/200511/toeic_emain.asp, 372, 120
    (I am using mySQL 4.0)

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    reverse the sequence of the SELECTS on the query

    the column names and datatypes of the result set of a union query are taken from the first SELECT encountered

    you could also override this by taking specific action on the first SELECT to name the result columns and give them a datatype wide enough for all values, e.g.
    Code:
    ( select s    as col1
           , id   as col2
           , cast(pw as varchar(15)) as col3
           , img  as col4
           , link
           , width
           , height
        from banner_advertizement )
    union all
    ( select m,o,top_s,say_search,url,null,null
        from text_advertizement )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    ( select s    as col1
           , id   as col2
           , cast(pw as varchar(15)) as col3
           , img  as col4
           , link
           , width
           , height
        from banner_advertizement )
    union all
    ( select m,o,top_s,say_search,url,null,null
        from text_advertizement )
    I am afraid It causes Error Executing Database Query in select s col1.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    try running each of the SELECTs separately to pinpoint the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    ( select s as col1
           , id  as col2
           , pw as col3
           , img  as col4
           , link
           , width
           , height
        from banner_advertizement )
    union
    ( select m,o,top_s,say_search,url,null,null
    from text_advertizement )
    The code above has no error but it produces "white te" for col3 "white teeth."

    The code below produces Error Executing Database Query in (varchar(15)) as col3.

    Code:
    ( select s as col1
           , id  as col2
           , cast(pw as varchar(15)) as col3
     
           , img  as col4
           , link
           , width
           , height
        from banner_advertizement )
    union
    ( select m,o,top_s,say_search,url,null,null
    from text_advertizement )

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, you'd better forget about CAST then until you upgrade, i think it came in with 4.1

    meanwhile, try reversing the order of your SELECTs
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    well, you'd better forget about CAST then until you upgrade, i think it came in with 4.1

    meanwhile, try reversing the order of your SELECTs
    The code below which are reversed selects produces white teeth, but it doesn't produce the values of width and height.
    Code:
    reversed selects
    
    <cfquery datasource='ds' name='banner_advertizement'>
    ( select 
    m as col1,
    o as col2,
    top_s as col3,
    say_search as col4,
    url as col5,
    null as col6,
    null as col7
    from text_advertizement )
    union
    ( select s 
           , id  
           , pw 
           , img 
           , link
           , width
           , height
        from banner_advertizement )
    
    </cfquery>
    
    result
    
    2 101 white teeth
    keep your teeth white whiteplus.co.kr/KRMain/index/ViewLogic/index.php
    
    4 0 2
    cyber.jungchul.com/img/topmenu/logo_slogan.gif cyber.jungchul.com/event/200511/toeic_emain.asp

  8. #8
    SitePoint Guru
    Join Date
    Aug 2003
    Location
    CT
    Posts
    643
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As Rudy mentioned, when you UNION two queries together, the results table takes on a singular data type, of which comes from the first SELECT statement.

    You are getting 'te' instead of 'teeth' because the field is either not long enough or is an integer instead of a varchar field.

    Instead of selecting
    null as col6,
    null as col7

    try using
    0 as col6,
    0 as col7

    and with cfm later, trim out the 0's from your result set.

  9. #9
    Get my greedy down dotJoon's Avatar
    Join Date
    Apr 2003
    Location
    daejeon, South Korea
    Posts
    2,223
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by briansol
    As Rudy mentioned, when you UNION two queries together, the results table takes on a singular data type, of which comes from the first SELECT statement.

    You are getting 'te' instead of 'teeth' because the field is either not long enough or is an integer instead of a varchar field.

    Instead of selecting
    null as col6,
    null as col7

    try using
    0 as col6,
    0 as col7

    and with cfm later, trim out the 0's from your result set.
    briansol, it works fine.


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
  •