genespos genespos - 7 months ago 7
SQL Question

Query with union repeating same subquery into each row

In my DB I have 5 tables with details and, to extract details from each table, I need to join witn 3 more tables.

I also need to get (into the result query table) some data (always the same data for each

IdLite
) and add them to each row.

I wrote a query that work but it is very very slow so I can't use it.

I used
CONCAT
two times:

First time to get details from each detail table;

Second time to get data from others tables (these data are always the same).
Also data before
CONCAT
are always the same (for each
IdLite
).

How can I improve my query? I was thinking about using variables but I wasn't able to do it.

(
SELECT
t.Office,
li.Year,
li.RIFNUM,
1 IdTable,
li.IdLite,
IdLiteUser,
d1.IdEvent,
CONCAT_WS('_',
IFNULL(d1.TypeT,''),
IFNULL(d1.In_Date,''),
IFNULL(d1.Out_Date,'')
) AS Dett,
CONCAT_WS('_',
IFNULL(li.Section,''),
IFNULL(li.InDate,''),
IFNULL(li.Type,''),
IFNULL(li.Subject,''),
IFNULL(li.DCU_Instance,''),
IFNULL(li.DC_Instance,'')
) AS Essential
FROM TRDetails1 d1
INNER JOIN TRUserLite lU USING (IdLite)
INNER JOIN TRLite li USING (IdLite)
INNER JOIN TR t USING(IdOffice)
WHERE lU.IdUser= '@myVarInsertedEachTime'
AND lU.Status = 0
AND d1.IdEvent NOT IN (
SELECT IdEvent
FROM TRInfo
WHERE IdUser= '@myVarInsertedEachTime'
AND IdTable = 1
AND IdEvent = d1.IdEvent
AND Date > d1.Changed)
)
UNION
(
SELECT
t.Office,
li.Year,
li.RifNum,
2 IdTable,
li.IdLite,
IdLiteUser,
d2.IdEvent,
CONCAT_WS('_',
IFNULL(DocType,''),
IFNULL(d2.Number,''),
IFNULL(d2.Side,''),
IFNULL(d2.InDate,'')
) AS Dett,
CONCAT_WS('_',
IFNULL(li.Section,''),
IFNULL(li.InDate,''),
IFNULL(li.Type,''),
IFNULL(li.Subject,''),
IFNULL(li.DCU_Instance,''),
IFNULL(li.DC_Instance,'')
) AS Essential
FROM TRDetails2 d2
INNER JOIN TRUserLite lU USING (IdLite)
INNER JOIN TRLite li USING (IdLite)
INNER JOIN TR t USING(IdOffice)
WHERE lU.IdUser= '@myVarInsertedEachTime'
AND lU.Status = 0
AND d2.IdEvent NOT IN (
SELECT IdEvent
FROM TRInfo
WHERE IdUser= lU.IdUser
AND IdTable= 2)
)
UNION
(
SELECT
t.Office,
li.Year,
li.RifNum,
3 IdTable,
li.IdLite,
IdLiteUser,
d3.IdEvent,
CONCAT_WS('_',
IFNULL(d3.UdiDate,''),
IFNULL(d3.UdiType,''),
IFNULL(d3.UdiResult,''),
IFNULL(d3.Supervisor,''),
IFNULL(d3.GroupIn,''),
IFNULL(d3.ROrder,'')
) AS Dett,
CONCAT_WS('_',
IFNULL(li.Section,''),
IFNULL(li.InDate,''),
IFNULL(li.Type,''),
IFNULL(li.Subject,''),
IFNULL(li.DCU_Instance,''),
IFNULL(li.DC_Instance,'')
) AS Essential
FROM TRDetails3 d3
INNER JOIN TRUserLite lU USING (IdLite)
INNER JOIN TRLite li USING (IdLite)
INNER JOIN TR t USING(IdOffice)
WHERE lU.IdUser= '@myVarInsertedEachTime'
AND lU.Status = 0
AND d3.IdEvent NOT IN (
SELECT IdEvent
FROM TRInfo
WHERE IdUser= lU.IdUser
AND IdTable= 3)
)
UNION
(
SELECT
t.Office,
li.Year,
li.RifNum,
4 IdTable,
li.IdLite,
IdLiteUser,
d4.IdEvent,
CONCAT_WS('_',
IFNULL(d4.Type,''),
IFNULL(d4.Number,''),
IFNULL(d4.Supervisor,''),
IFNULL(d4.PubDate,''),
IFNULL(d4.UdiDate,''),
IFNULL(d4.UdiType,''),
IFNULL(d4.Result,'')
) AS Dett,
CONCAT_WS('_',
IFNULL(li.Section,''),
IFNULL(li.InDate,''),
IFNULL(li.Type,''),
IFNULL(li.Subject,''),
IFNULL(li.DCU_Instance,''),
IFNULL(li.DC_Instance,'')
) AS Essential
FROM TRDetails4 d4
INNER JOIN TRUserLite lU USING (IdLite)
INNER JOIN TRLite li USING (IdLite)
INNER JOIN TR t USING(IdOffice)
WHERE lU.IdUser= '@myVarInsertedEachTime'
AND lU.Status = 0
AND d4.IdEvent NOT IN (
SELECT IdEvent
FROM TRInfo
WHERE IdUser= lU.IdUser
AND IdTable= 4)
)
UNION
(
SELECT
t.Office,
li.Year,
li.RifNum,
5 IdTable,
li.IdLite,
IdLiteUser,
d5.IdEvent,
CONCAT_WS('_',
IFNULL(d5.Type,''),
IFNULL(d5.Number,''),
IFNULL(d5.Supervisor,''),
IFNULL(d5.PubDate,''),
IFNULL(d5.Result,'')
) AS Dett,
CONCAT_WS('_',
IFNULL(li.Section,''),
IFNULL(li.InDate,''),
IFNULL(li.Type,''),
IFNULL(li.Subject,''),
IFNULL(li.DCU_Instance,''),
IFNULL(li.DC_Instance,'')
) AS Essential
FROM TRDetails5 d5
INNER JOIN TRUserLite lU USING (IdLite)
INNER JOIN TRLite li USING (IdLite)
INNER JOIN TR t USING(IdOffice)
WHERE lU.Idutente = '@myVarInsertedEachTime'
AND lU.Status = 0
AND d5.IdEvent NOT IN (
SELECT IdEvent
FROM TRInfo
WHERE IdUser= lU.Idutente
AND IdTable= 5)
)
ORDER BY Office, Year, RifNum, IdTable


Edit:

The structure of my DB is:

IdLite
is the id of basic data.

Each
IdLite
has One
Office
, One
Year
, One
RifNum
, One
TR
.

Each
IdLite
has Many records on each of 5 tables of details (
TRDetails1
,
TRDetails2
,
TRDetails3
,
TRDetails4
,
TRDetails5
).

Each
User
has many
IdLite
(stored by ref in
TRUserLite
) and need to know each
event
(stored into each of the 5 tables) of each
IdLite
.

When an
User
knows a detail it (rif) is stored into
TRInfo


I need to retrieve events (from all 5 tables) interesting each
IdLite
(linked to a specific
User
) that are not known by him.

Edit2:

These are my tables structures:

CREATE TABLE IF NOT EXISTS `TRDetails1` (
`IdEvent` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`IdLite` bigint(20) NOT NULL,
`TypeT` varchar(50) DEFAULT NULL,
`In_Date` date DEFAULT NULL,
`Out_Date` date DEFAULT NULL,
`Changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRDetails2` (
`IdEvent` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`IdLite` bigint(20) NOT NULL,
`DocType` varchar(200) DEFAULT NULL,
`Number` int(10) DEFAULT NULL,
`Side` varchar(50) DEFAULT NULL,
`InDate` date DEFAULT NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRDetails3` (
`IdEvent` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`IdLite` bigint(20) NOT NULL,
`UdiDate` date DEFAULT NULL,
`UdiType` varchar(50) DEFAULT NULL,
`UdiResult` varchar(200) DEFAULT NULL,
`Supervisor` varchar(50) DEFAULT NULL,
`GroupIn` varchar(50) DEFAULT NULL,
`ROrder` int(5) DEFAULT NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRDetails4` (
`IdEvent` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`IdLite` bigint(20) NOT NULL,
`TypeT` varchar(50) DEFAULT NULL,
`Number` int(10) DEFAULT NULL,
`Supervisor` varchar(50) DEFAULT NULL,
`PubDate` date DEFAULT NULL,
`UdiDate` date DEFAULT NULL,
`UdiType` varchar(50) DEFAULT NULL,
`Result` varchar(50) DEFAULT NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRDetails5` (
`IdEvent` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`IdLite` bigint(20) NOT NULL,
`TypeT` varchar(50) DEFAULT NULL,
`Number` int(10) DEFAULT NULL,
`Supervisor` varchar(50) DEFAULT NULL,
`PubDate` date DEFAULT NULL,
`Result` varchar(50) DEFAULT NULL,
`Updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRLite` (
`IdLite` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`Region` char(30) NOT NULL,
`IdOffice` smallint(4) NOT NULL,
`RIFNUM` int(11) NOT NULL,
`Year` mediumint(4) NOT NULL,
`Sub` tinyint(2) DEFAULT NULL,
`Section` tinyint(2) NOT NULL,
`InDate` date NOT NULL,
`Type` varchar(50) NOT NULL,
`Subject` varchar(200) NOT NULL,
`DCU_Instance` char(1) DEFAULT NULL,
`DC_Instance` char(1) DEFAULT NULL,
`Defined` tinyint(2) DEFAULT NULL,
`DefinedDate` date DEFAULT NULL,
`Updated` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
`Scroll_Table` set

('00001','00010','00011','00100','00101','00110','00111','01000','01001','01010','01011','01100','01101

','01110','01111','10000','10001','10010','10011','10100','10101','10110','10111','11000','11001','1101

0','11011','11100','11101','11110','11111') DEFAULT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRUserLite` (
`IdUser` bigint(20) NOT NULL,
`IdLite` bigint(20) NOT NULL,
`IdLiteUser` varchar(150) DEFAULT NULL,
`Status` tinyint(5) NOT NULL DEFAULT '0',
PRIMARY KEY (`IdUser`,`IdLite`),
KEY `IdLite` (`IdLite`)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TR` (
`IdOffice` smallint(4) NOT NULL PRIMARY KEY,
`IdRegion` tinyint(2) NOT NULL,
`Office` char(200) NOT NULL,
`IdWebOffice` varchar(30) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `TRInfo` (
`IdInfo` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`IdUser` bigint(20) NOT NULL,
`IdLite` bigint(20) NOT NULL,
`IdTable` tinyint(1) NOT NULL,
`IdEvent` bigint(20) NOT NULL,
`InfoDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `Users` (
`IdUser` bigint(20) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`User` varchar(50) NOT NULL,
`Status` tinyint(5) NOT NULL DEFAULT '0'
) ENGINE=InnoDB;

ALTER TABLE `TRUserLite`
ADD CONSTRAINT `TRUserLite_ibfk_1` FOREIGN KEY (`IdUser`) REFERENCES `Users` (`IdUser`),
ADD CONSTRAINT `TRUserLite_ibfk_2` FOREIGN KEY (`IdLite`) REFERENCES `TRLite` (`IdLite`);


EDIT 3: (DB inserting data criteria)

Each IdLite can have many details (on one or more of the 5 detail tables).

When something happens I insert into one (or more) detail table a new record.

After that, each User needs to know (only) new details.

To do this I keep known details into TRInfo and I give to each user only new Details (referring to IdLite linked to the User).

So I need to extract all details not known.

EDIT 4: (how to modify DB inserting criteria)

If I understand your tips, you are suggesting to insert into TRInfo before that a detail is known, and add a field to check if the relative user has known it.
So I'll have all details into TRInfo and I'll cut off the ones already known.
Is this the way you're telling me to do?

Answer

WOW... I really had to scratch my head on this one, and I see why nobody else even tried to help you on it. Probably more confusing than simple query / aggregates. Anyhow, let me see if I understand what APPEARS to be the flow of these tables. An entry is added to any one of the Detail tables 1-5. These are assigned to certain users. Based on a task at hand, when they are ultimately completed, a record is inserted into the TRInfo thus closing the event. For this reason, you are STARTING with each detail table and then applying a WHERE NOT IN query to the TRINFO table

Now, that said, you are looking for transactions associated to a given user. From here, we can START directly with the TRUserLite table.

From TRUserLite table, we can join to the respective Details tables 1-5 on the IdLite ID, while preserving the IdEvent from the detail table and its corresponding 1-5 value where the event came from. Finally, we can do a LEFT-JOIN to the TRInfo table for the same user, event and table ID #. By doing a left-join it is faster than a NOT IN subselect. We eliminate those records already "completed" by including in the WHERE clause

     AND TRInfo.IdEvent IS NULL

Now, all above being said, and if accurate, the following query SHOULD help you out. It MAY have some minor syntax issues which you would probably be able to resolve (alias name, field, etc).

Now I'll explain the query. To simplify ALL the extra joins of your original query, I am starting with the TRUserLite and grabbing those entries that qualify without the additional join to the TRLite and TR tables. I am just joining / UNIONing them to the details and TRInfo to get those records that qualify. While joined to the Details table, I get the respective concatenated fields as a common "Dett" result column. Once all the qualified records are complete, only THEN to I join back to the TRLite and TR to pull the additional data for the final query. This keeps the inner query tighter to limited details, THEN pull the rest.

Recommended indexes
table          index
TRUserLite     ( IdUser, Status )     to optimize the WHERE clause.
TRInfo         ( IdUser, IdLite, IdEvent, IdTable )

SELECT 
      TR.Office,
      TRL.`Year`,
      TRL.RIFNum,
      All5.IdTable
      All5.IdLite,
      All5.IdLiteUser,
      All5.IdEvent,
      All5.IdUser,
      All5.Dett,
      U.User,
      U.Status as UserStatus,
      CONCAT_WS('_', 
         IFNULL( TRL.Section,''), 
         IFNULL( TRL.InDate,''), 
         IFNULL( TRL.Type,''), 
         IFNULL( TRL.Subject,''), 
         IFNULL( TRL.DCU_Instance,''), 
         IFNULL( TRL.DC_Instance,'') ) AS Essential
   from 
      ( SELECT 
              TRUL.IDUser,
              TRUL.IdLite,
              TRUL.IdLiteUser,
              1 as IdTable,
              TD1.IdEvent,
              CONCAT_WS('_',
                 IFNULL( TD1.TypeT,''), 
                 IFNULL( TD1.In_Date,''), 
                 IFNULL( TD1.Out_Date,'') ) AS Dett
           from
              TRUserLite TRUL
                 JOIN TRDetails1 TD1
                    ON TRUL.IdLite = TD1.IdLite
                    LEFT JOIN TRInfo
                       ON TRUL.IdUser = TRInfo.IdUser
                       AND TRUL.IdLite = TRInfo.IdLite
                       AND TD1.IdEvent = TRInfo.IdEvent
                       AND TRInfo.IdTable = 1
           where 
                 TRUL.IdUser = '@myVarInsertedEachTime'
             AND TRUL.Status = 0
             AND TRInfo.IdEvent IS NULL

        UNION ALL
        SELECT 
              TRUL.IDUser,
              TRUL.IdLite,
              TRUL.IdLiteUser,
              2 as IdTable,
              TD2.IdEvent,
              CONCAT_WS( '_', 
                 IFNULL( TD2.DocType,''), 
                 IFNULL( TD2.Number,''), 
                 IFNULL( TD2.Side,''), 
                 IFNULL( TD2.InDate,'') ) AS Dett
           from
              TRUserLite TRUL
                 JOIN TRDetails2 TD2
                    ON TRUL.IdLite = TD2.IdLite
                    LEFT JOIN TRInfo
                       ON TRUL.IdUser = TRInfo.IdUser
                       AND TRUL.IdLite = TRInfo.IdLite
                       AND TD2.IdEvent = TRInfo.IdEvent
                       AND TRInfo.IdTable = 2
           where 
                 TRUL.IdUser = '@myVarInsertedEachTime'
             AND TRUL.Status = 0
             AND TRInfo.IdEvent IS NULL

        UNION ALL
        SELECT 
              TRUL.IDUser,
              TRUL.IdLite,
              TRUL.IdLiteUser,
              3 as IdTable,
              TD3.IdEvent,
              CONCAT_WS('_', 
                 IFNULL( TD3.UdiDate,''), 
                 IFNULL( TD3.UdiType,''), 
                 IFNULL( TD3.UdiResult,''), 
                 IFNULL( TD3.Supervisor,''), 
                 IFNULL( TD3.GroupIn,''), 
                 IFNULL( TD3.ROrder,'') ) AS Dett
           from
              TRUserLite TRUL
                 JOIN TRDetails3 TD3
                    ON TRUL.IdLite = TD3.IdLite
                    LEFT JOIN TRInfo
                       ON TRUL.IdUser = TRInfo.IdUser
                       AND TRUL.IdLite = TRInfo.IdLite
                       AND TD3.IdEvent = TRInfo.IdEvent
                       AND TRInfo.IdTable = 3
           where 
                 TRUL.IdUser = '@myVarInsertedEachTime'
             AND TRUL.Status = 0
             AND TRInfo.IdEvent IS NULL

        UNION ALL
        SELECT 
              TRUL.IDUser,
              TRUL.IdLite,
              TRUL.IdLiteUser,
              4 as IdTable,
              TD4.IdEvent,
              CONCAT_WS('_', 
                 IFNULL( TD4.Type,''), 
                 IFNULL( TD4.Number,''), 
                 IFNULL( TD4.Supervisor,''), 
                 IFNULL( TD4.PubDate,''), 
                 IFNULL( TD4.UdiDate,''), 
                 IFNULL( TD4.UdiType,''), 
                 IFNULL( TD4.Result,'') ) AS Dett 
           from
              TRUserLite TRUL
                 JOIN TRDetails4 TD4
                    ON TRUL.IdLite = TD4.IdLite
                    LEFT JOIN TRInfo
                       ON TRUL.IdUser = TRInfo.IdUser
                       AND TRUL.IdLite = TRInfo.IdLite
                       AND TD4.IdEvent = TRInfo.IdEvent
                       AND TRInfo.IdTable = 4
           where 
                 TRUL.IdUser = '@myVarInsertedEachTime'
             AND TRUL.Status = 0
             AND TRInfo.IdEvent IS NULL

        UNION ALL
        SELECT 
              TRUL.IDUser,
              TRUL.IdLite,
              TRUL.IdLiteUser,
              5 as IdTable,
              TD5.IdEvent,
              CONCAT_WS('_', 
                 IFNULL( TD5.Type,''), 
                 IFNULL( TD5.Number,''), 
                 IFNULL( TD5.Supervisor,''), 
                 IFNULL( TD5.PubDate,''), 
                 IFNULL( TD5.Result,'') ) AS Dett
           from
              TRUserLite TRUL
                 JOIN TRDetails5 TD5
                    ON TRUL.IdLite = TD5.IdLite
                    LEFT JOIN TRInfo
                       ON TRUL.IdUser = TRInfo.IdUser
                       AND TRUL.IdLite = TRInfo.IdLite
                       AND TD5.IdEvent = TRInfo.IdEvent
                       AND TRInfo.IdTable = 5
           where 
                 TRUL.IdUser = '@myVarInsertedEachTime'
             AND TRUL.Status = 0
             AND TRInfo.IdEvent IS NULL ) All5

         JOIN Users U
            ON All5.IdUser = U.IdUser

         JOIN TRLite TRL
            ON All5.IdLite = TRL.IdLite
            JOIN TR
               ON TRL.IdOffice = TR.IdOffice
   ORDER BY
      TR.Office,
      TRL.`Year`,
      TRL.RIFNum,
      All5.IdTable

(BTW, I added the User table to the join in case you wanted those columns as well... Can be easily removed)

It would be interesting to know the performance time of your original query to the result of this one, even if it does not resolve your query time.

FEEDBACK

You are currently looking for things NOT IN the TRInfo table. If you could possibly restructure it so that you add a status column to TRInfo such as "IsRecordOpen" (exaggerating the column name) and have it set to 1, and at that time, insert into the TRDetails table. Then when it is completed, set the flag to zero (no longer an open task). So THIS is the basis of each item pending work to be completed and it points to the respective detail table by its event ID. Then your query would be simplified down to something like..

select
         TR.Office,
          TRL.`Year`,
          TRL.RIFNum,
          TRI.IdTable
          TRI.IdLite,
          TRUL.IdLiteUser,
          TRI.IdEvent,
          TRI.IdUser,
          case when TRI.IdTable = 1 THEN
                  CONCAT_WS('_',
                     IFNULL( TD1.TypeT,''), 
                     IFNULL( TD1.In_Date,''), 
                     IFNULL( TD1.Out_Date,'') )

               when TRI.IdTable = 2 THEN
                  CONCAT_WS( '_', 
                     IFNULL( TD2.DocType,''), 
                     IFNULL( TD2.Number,''), 
                     IFNULL( TD2.Side,''), 
                     IFNULL( TD2.InDate,'') ) 

               when TRI.IdTable = 3 THEN
                  CONCAT_WS('_', 
                     IFNULL( TD3.UdiDate,''), 
                     IFNULL( TD3.UdiType,''), 
                     IFNULL( TD3.UdiResult,''), 
                     IFNULL( TD3.Supervisor,''), 
                     IFNULL( TD3.GroupIn,''), 
                     IFNULL( TD3.ROrder,'') ) 

               when TRI.IdTable = 4 THEN
                  CONCAT_WS('_', 
                     IFNULL( TD4.Type,''), 
                     IFNULL( TD4.Number,''), 
                     IFNULL( TD4.Supervisor,''), 
                     IFNULL( TD4.PubDate,''), 
                     IFNULL( TD4.UdiDate,''), 
                     IFNULL( TD4.UdiType,''), 
                     IFNULL( TD4.Result,'') )

               when TRI.IdTable = 5 THEN
                  CONCAT_WS('_', 
                     IFNULL( TD5.Type,''), 
                     IFNULL( TD5.Number,''), 
                     IFNULL( TD5.Supervisor,''), 
                     IFNULL( TD5.PubDate,''), 
                     IFNULL( TD5.Result,'') ) 

               END AS Dett,

          CONCAT_WS('_', 
             IFNULL( TRL.Section,''), 
             IFNULL( TRL.InDate,''), 
             IFNULL( TRL.Type,''), 
             IFNULL( TRL.Subject,''), 
             IFNULL( TRL.DCU_Instance,''), 
             IFNULL( TRL.DC_Instance,'') ) AS Essential
   from
      TRInfo TRI
         JOIN TRLite TRL
            on TRI.IdLite = TRL.IdLite
            JOIN TR
               on TRL.IdOffice = TR.IdOffice

         JOIN TRUserLite TRUL
            on TRI.IdUser = TRUL.IdUser
           AND TRI.IdLite = TRUL.IdLite
           AND TRUL.Status = 0

         LEFT JOIN TRDetails1 TD1
            ON TRI.TableID = 1
           AND TRI.IdEvent = TD1.IdEvent
         LEFT JOIN TRDetails2 TD2
            ON TRI.TableID = 2
           AND TRI.IdEvent = TD2.IdEvent
         LEFT JOIN TRDetails1 TD3
            ON TRI.TableID = 3
           AND TRI.IdEvent = TD3.IdEvent
         LEFT JOIN TRDetails1 TD4
            ON TRI.TableID = 4
           AND TRI.IdEvent = TD4.IdEvent
         LEFT JOIN TRDetails1 TD5
            ON TRI.TableID = 5
           AND TRI.IdEvent = TD5.IdEvent
   where
          TRI.IdUser = 'the user ID you want'
      AND TRI.IsRecordOpen
Comments