SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Combine fields in Order clause ?

    Hi guys.

    I have two tables, table one has two fields (amoungst others) that hold a numeric value, table two has one field (amoungst others) that also has a numeric value.

    These numeric values are assigned to the 3 fields from a central count, therefore numbers are never repeated once a number is issued to one of the fields the count will increase.

    I need to produce a result that gives me all of these numeric values (with other assiciated data from those records) in asscending order.

    Im assumming Id perhaps need to create a temporary table but am unsure the best way to go about this.

    Any help appreciated
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  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)
    please show a few sample rows with these numbers, and show how you want them sorted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Sorry Rudy, I wasnt clear

    The numbers are all mandate numbers starting at 1 and increasing in sequence

    These numbers are distributed by the existing system into two tables, a property table and a buyers table. A mandate exists for each property that is being sold and for each buyer who has commissioned the agency to search for a house on their behalf. Hence the two tables.

    To further complicate the property table, on a change of selling price the mandate has to be reissued and the old one is annulled. The system currently deals with this by issuing a new mandate number for the property and placing the old mandate number into another field just to keep a record of it.

    What I need to do is to produce a table that collates all these numbers, property mandates, buyers mandates and old property mandates, and display them in mandate order.

    Lots of other fields in both tables but the ones that concern this question are

    Property_t
    ID
    Mandate_No
    Old_Mandate_No

    Buyers_t
    ID
    Mandate_No

    Hope thats clearer

    edit: in an ideal world the mandate numbers would have their own table and then be referenced to the property or buyers id, but the system is already in place and this requirement is an add on to an add on, if its too complicated to do, or even not do-able I'll have to rethink the method of producing this data and sort the table rows using PHP.
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  4. #4
    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)
    what i was hoping for was a few sample rows of data, in which you would actually show me some values in those 3 columns, so that i could see how they are to be sorted

    i'm particularly interested in seeing where a row that has both a mandate number and an old mandate number shows up

    in other words, there will likely be some conditional processing, maybe a CASE expression or something, that indicates which of the two values in the same row is used for sorting

    alternatively, perhaps you intend a row which has both a mandate number and an old mandate number to actually show up twice in the sorted results, and for this there would need to be a different solution

    this is the part of the problem that you have not fully explained
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Im using ficticious data, as its quicker to do this than strip real data to post here. Im assuming the that the mandate nos start from 1000

    Code:
     
     
    buyers_t
    ID      Mandate_No
    245         1001
    564         1006
    965         1009
     
     
    property_t
    ID          Mandate_No        Old_Mandate_No
    321            1000
    322            1005                      1002
    323            1003
    324            1010                      1004
    325            1007
    326            1008
    table required to follow
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  6. #6
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    cont.

    Table I require

    Code:
    Mandate No        Details        
    1000               Property 321
    1001               Buyer 245
    1002               Avenant    Property 322
    1003               Property 323
    1004               Avenant    Property 324
    1005               Property 322   Old Mandate=1002
    1006               Buyer 564
    1007               Property 325
    1008               Property 326
    1009               Buyer 965
    1010               Property 324   Old Mandate=1004
    Avenant = a mandate thats been annulled and reissued to a new mandate number


    Thanks Rudy
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  7. #7
    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)
    Code:
    SELECT Mandate_No
         , CONCAT('Buyer ',ID) AS Details
      FROM buyers_t
    UNION ALL
    SELECT Mandate_No
         , CONCAT('Property ',ID) AS Details
      FROM property_t
     WHERE COALESCE(Old_Mandate_No,'') = ''
    UNION ALL
    SELECT Mandate_No
         , CONCAT('Property ',ID
            ,' Old Mandate=',Old_Mandate_No) AS Details
      FROM property_t
     WHERE COALESCE(Old_Mandate_No,'') > ''
    UNION ALL
    SELECT Old_Mandate_No
         , CONCAT('Avenant    Property ',ID) AS Details
      FROM property_t
     WHERE COALESCE(Old_Mandate_No,'') > ''
    ORDER
        BY Mandate_No
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Wow, thanks Rudy,

    and if I wanted to specify a starting mandate number, would that 'Where' have to be added to each select in the union ?
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  9. #9
    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)
    that'd be one way to do it, yes

    here's another...
    Code:
    SELECT * 
      FROM ( SELECT Mandate_No
                  , CONCAT('Buyer ',ID) AS Details
               FROM buyers_t
             UNION ALL
             SELECT Mandate_No
                  , CONCAT('Property ',ID) AS Details
               FROM property_t
              WHERE COALESCE(Old_Mandate_No,'') = ''
             UNION ALL
             SELECT Mandate_No
                  , CONCAT('Property ',ID
                     ,' Old Mandate=',Old_Mandate_No) AS Details
               FROM property_t
              WHERE COALESCE(Old_Mandate_No,'') > ''
             UNION ALL
             SELECT Old_Mandate_No
                  , CONCAT('Avenant    Property ',ID) AS Details
               FROM property_t
              WHERE COALESCE(Old_Mandate_No,'') > ''
           ) AS u
     WHERE Mandate_No > 937
    ORDER
        BY Mandate_No
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    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)
    by the way, that was one of the very few instances where it is actually okay to use the dreaded, evil "select star"

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

  11. #11
    Who turned the lights out !! Mandes's Avatar
    Join Date
    May 2005
    Location
    S.W. France
    Posts
    2,496
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Great, thanks again Rudy.

    Im having a few problems but im sure thats down to the consistancy of the clients data, rather than the query :-(
    A Little Knowledge Is A Very Dangerous Thing.......
    That Makes Me A Lethal Weapon !!!!!!!!

    Contract PHP Programming

  12. #12
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Evil SELECT * vs Evil ENUM

    Discuss. ;-)

  13. #13
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2008
    Posts
    5,757
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


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
  •