SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist Fleeters's Avatar
    Join Date
    Jul 2003
    Location
    Dumpsville
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    join to address, billing, and shipping (was "help with difficult query")

    So I need some help with this query. heres what the tables look like:

    user:
    user_id,
    serial,
    email,
    first_name,
    last_name,
    occupation,
    companyname,
    address_id,
    billing_address_id,
    shipping_address_id,
    level,
    status

    address
    address_id,
    address_1,
    address_2,
    city,
    state


    So heres the dilema. Our application has a csv export option that dumps selected records to a csv file. The way it works right now is we get a user_id. Select a row from the user table and do the necessary processing for a csv file. (csv = comma seperated values). In the csv file all the user columns are returned where that user_id matches.

    ex csv output: 1,,aaron@smi.com,aaron,s,flashdev,,2,4,5,,1

    What I need to have added to the csv dump is address from the address table (not just the address_id's ( above 2,4,5 ) ). So say we get a user_id, look it up in the user_table. get that row, then for address_id, billing_address_id and shipping_address_id I need to select each of those rows from the address table where the address_id is 2,4, or 5. and include it into one query.

    here is the query that is getting the current csv dump:
    "SELECT * FROM user WHERE user_id = $id"

    I don't know where to go from here as far as weird joins or sub selects. The thing I'm getting confused about is that it all has to be in one query.

    also when getting the address info from the address table all the columns can just be CONCAT'd into one string with an alias of address, shipping_address, billing_address...

    hope you can understand this and hope you can help!

    thanks.
    Aaron Smith
    smithaaronlee.net

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select user_id
         , serial
         , email
         , first_name
         , last_name
         , occupation
         , companyname
         , concat_ws(''
                 ,coalesce(a.address_1,'')
                 ,coalesce(concat(' ',a.address_2),'')
                 ,coalesce(concat(' ',a.city),'')
                 ,coalesce(concat(' ',a.state),'')    ) as address
         , concat_ws(''
                 ,coalesce(b.address_1,'')
                 ,coalesce(concat(' ',b.address_2),'')
                 ,coalesce(concat(' ',b.city),'')
                 ,coalesce(concat(' ',b.state),'')    ) as billing_address
         , concat_ws(''
                 ,coalesce(s.address_1,'')
                 ,coalesce(concat(' ',s.address_2),'')
                 ,coalesce(concat(' ',s.city),'')
                 ,coalesce(concat(' ',s.state),'')    ) as shipping_address
      from user 
    left outer
      join address as a
        on a.address_id = user.address_id 
    left outer
      join address as b
        on b.address_id = user.billing_address_id     
    left outer
      join address as s
        on s.address_id = user.shipping_address_id     
     WHERE user_id = $id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Evangelist Fleeters's Avatar
    Join Date
    Jul 2003
    Location
    Dumpsville
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if that works you are a guru! i'll let you know
    Aaron Smith
    smithaaronlee.net

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    do not use *
    Code:
    select u.user_id
         , u.serial
         , u.email
         , u.first_name
         , u.last_name
         , u.occupation
         , u.companyname
         , u.address_id
         , u.address_id
         , billing.address_1
         , billing.address_2
         , billing.city
         , billing.state
         , shipping.address_1
         , shipping.address_2
         , shipping.city
         , shipping.state
         , u.level
         , u.status
      from user
      join address billing
        on billing.address_id = u.billing_address_id
      join address shipping
        on shipping.address_id = u.shipping_address_id

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    rudy- why so complicated?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by longneck
    rudy- why so complicated?
    well, to start with, you need three joins, not two

    and he did say to concat, so i did

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, come to think of it, i want the spaces trailing, don't i...
    Code:
         , concat_ws(''
                 ,coalesce(concat(a.address_1,' '),'')
                 ,coalesce(concat(a.address_2,' '),'')
                 ,coalesce(concat(a.city     ,' '),'')
                 ,coalesce(a.state,'')   ) as address
         , concat_ws(''
                 ,coalesce(concat(b.address_1,' '),'')
                 ,coalesce(concat(b.address_2,' '),'')
                 ,coalesce(concat(b.city     ,' '),'')
                 ,coalesce(b.state,'')   ) as billing_address
         , concat_ws(''
                 ,coalesce(concat(s.address_1,' '),'')
                 ,coalesce(concat(s.address_2,' '),'')
                 ,coalesce(concat(s.city     ,' '),'')
                 ,coalesce(s.state,'')   ) as shipping_address
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah, i didn't notice that first address id.

    and i see what you're doing now: you're pre-assembling the columns for the CSV file. that could be dangerous if the code that creates the CSV is smart because it will detect commas and spaces that need to be quoted.

  9. #9
    SitePoint Evangelist Fleeters's Avatar
    Join Date
    Jul 2003
    Location
    Dumpsville
    Posts
    406
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you rock.. it was correct... thanks a lot
    Aaron Smith
    smithaaronlee.net


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
  •