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

please show a few sample rows with these numbers, and show how you want them sorted

Sorry Rudy, I wasnt clear :blush:

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.

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

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

 
 
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

cont.

Table I require


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

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

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 ?

that’d be one way to do it, yes :slight_smile:

here’s another…

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

:cool:

by the way, that was one of the very few instances where it is actually okay to use the dreaded, evil “select star”

:slight_smile:

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 :frowning:

Evil SELECT * vs Evil ENUM

Discuss. :wink: