Fake records in MySQL

[shakespeare5677]

Hi,
I'm working with MySQL and here's the problem:

I have two tables: RecordTable and DetailTable. The important fields in Record table are ID, Date, Time, Deleted; and the DetailTable consists of ID, RecordID, Number, Class, Details and Deleted.

I was able to produce a nice little list where within each time period and date, I had a group of details per record with the data records (fake id=1) first and 2 fake records (fake ids=2 and 3) at the end:

time
->date
-->record
---->1
---->1
---->1
---->2
---->3
-->record
---->1
---->1
---->1
---->2
---->3
and so on

by using a fake table filled with data like this
1,0
2,0
3,0

and the following query

SELECT RecordTable.Date, RecordTable.Time,
       DetailTable.Class, DetailTable.Detail, 
       FakeTable.ID
FROM (RecordTable INNER JOIN DetailTable
      ON RecordTable.ID = DetailTable.RecordID)
      LEFT JOIN FakeTable
      ON RecordTable.Deleted = FakeTable.Field2
WHERE ((FakeTable.ID=SOME_ID
        AND DetailTable.Class=SOME_CLASS)
    OR (RecordTable.Deleted=0
        AND DetailTable.Number=1
        AND FakeTable.ID IN (2,3)))
    AND rundownitem.deleted=0
ORDER BY RecordTable.Time,
    RecordTable.Date, FakeTable.ID;

The problem is that now the boss wants all detail classes to be included, not just one and I still need the records to come in the same order as before (because those fake records are used for strange calculations in the report), now belonging to their class groups:

class
-->time
---->date
------>record
-------->1
-------->1
-------->1
-------->2
-------->3
------>record
-------->1
-------->1
-------->1
-------->2
-------->3

I need to change the query but I'm all out of ideas. Please help.

-Max

 

[juliane26]

this is a little confusing.
could you just post sample data from record and detail table and the output you expect also as a table ??

because you do have this fake table and enter/select the fake ID (SOME_ID), but actually this does not make business sense, since it is fake ?


Juliane

 

[shakespeare5677]

Sorry, it is a bit confusing. The reason why I need those two fake detail rows at the end of each record is not business related - it is programming related. There's a very ugly but working process producing a report in crystal reports, and I'm using the two fake records to "execute" event calculations. Although very ugly, that report is very powerful, very promising and practically cannot be changed (unless I change the sql command retrieving the data).

The records in the fake table are truly fake; they do not change and are only present to allow me to duplicate data depending on the link between tables. My problem is, though I can easily duplicate the data using those tables, I need to filter and order the records in the
class
-->time
---->date
------>record
-------->1
-------->1
-------->1
-------->2
-------->3
------>record
-------->1
-------->1
-------->1
-------->2
-------->3
order.

Thanks for you help.
-Max

 

[lespaul]

again, can you post some sample data and what results you want from that sample?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[shakespeare5677]

I actually got it just now by using some funky where statements, but it works. Thanks.
-Max