SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Gurus: Please help with join

    Hi All,
    I have 3 tables...
    The CROP table being the main tabke, SOLD_NUMERIC table stores numberic values and SOLD_Character tabke stores string values.

    CROP TABLE

    CropID | LandID
    ----------------
    CC001 | 00001
    CC002 | 00001
    CC003 | 00001
    CC004 | 00001
    PP001 | 00002
    PP002 | 00002
    TB001 | 00003
    TB002 | 00003


    SOLD_NUMERIC TABLE

    CropID | CurrencyI | Value
    --------------------------
    CC001 | Dollar | 500
    CC003 | Cedis | 2000
    CC004 | Dollar | 200
    PP002 | Dollar | 320
    TB001 | Dollar | 4000


    SOLD_CHARACTER TABLE

    CropID | CurrencyI | Value
    --------------------------
    CC002 | Dollar | Pending
    TB002 | Dollar | Blocked


    Now, the problem is how to write the query
    to get an output like the one below based structure of my table and the values given here.

    CropID | DollarI | DollarC | Cedis
    ---------------------------------
    CC001 | 500 | NULL | NULL
    CC002 | NULL | Pending | NULL
    CC003 | NULL | NULL | 2000
    CC004 | 200 | NULL | NULL
    PP002 | 320 | NULL | NULL
    TB001 | 4000 | NULL | NULL
    TB002 | NULL | Blocked | NULL

    Thanks

    Frederick

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    very very weird table structure
    Code:
    select C.CropID 
         , case when SN.CurrencyI = 'Dollar'
                then SN.Value
              end as DollarI 
         , case when SC.CurrencyI = 'Dollar'
                then SC.Value
              end as DollarC
         , case when SN.CurrencyI = 'Cedis'
                then SN.Value
              end as Cedis
      from CROP as C
    left outer
      join SOLD_NUMERIC as SN
        on C.CropID = SN.CropID 
    left outer
      join SOLD_CHARACTER as SC  
        on C.CropID = SC.CropID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot fredmintah's Avatar
    Join Date
    Jun 2004
    Location
    Ghana
    Posts
    102
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thanks

    Thanks you very much. it getting what I'm suppose to be getting.

    Frederick


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
  •