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.
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
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
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