CODEWITHSUNDEEP
sqlms-accessselect
Botz3000
Botz3000

Reputation: 39640

Selecting a number of related records into a result row

I am currently writing an export function for an MS-Access database and i am not quite sure how to write a query that gives me the results that i want.
What i am trying to do is the following:

Let's say i have a table Error and there is a many-to-many relationship to the table Cause, modeled by the table ErrorCause. Currently i have a query similar to this (simplified, the original also goes one relationship further): 

select Error.ID, Cause.ID
from ((Error inner join ErrorCauses on Error.ID = ErrorCauses.Error)
      left join Cause on ErrorCauses.Cause = Cause.ID)

I get something like this: 

 Error | Cause
 -------------
 12345 | 12    
 12345 | 23    
 67890 | 23    
 67890 | 34

But i need to select the IDs of the first, say, 3 Causes for each error (even if those are empty), so that it looks like this: 

 Error | Cause1 | Cause2 | Cause3
 --------------------------------
 12345 | 12     | 23     |       
 67890 | 23     | 34     |

Is there any way to do this in a single query? Like selecting the Top 3 and then flattening this into the resulting row? Thanks in advance for any pointers.

Upvotes: 1

Views: 152

Answers (1)

Yawar
Yawar

Reputation: 11637

Your requirement is for a specific number of causes--3. This makes it possible and manageable to get three different causes on the same row by doing a three-way join on the same subquery.

First, let's define your error-and-cause query as a straight-up Access query (a QueryDef object, if you want to be technical).

qryErrorCauseInfo:

select
  Error.ID as ErrorID
, Cause.ID as CauseID
from (Error
inner join ErrorCauses
on Error.ID = ErrorCauses.Error)
left outer join Cause
on ErrorCauses.Cause = Cause.ID

By the way, I feel that the above left join should really be an inner join, for the reason I mentioned in my comment.

Next, let's do a three-way join to get possible combinations of causes in rows:

qryTotalCause:

select distinct
  *
, iif(Cause1 is null, 0, 1)
+ iif(Cause2 is null, 0, 1)
+ iif(Cause3 is null, 0, 1) as TotalCause
from (
  select
    eci1.ErrorID
  , eci1.CauseID as Cause1
  , iif(eci2.CauseID = Cause1, null, eci2.CauseID) as Cause2
  , iif(
      eci3.CauseID = Cause1 or eci3.CauseID = Cause2
    , null
    , eci3.CauseID
    ) as Cause3
  from (qryErrorCauseInfo as eci1
  left outer join qryErrorCauseInfo as eci2
  on eci1.ErrorID = eci2.ErrorID)
  left outer join qryErrorCauseInfo as eci3
  on eci2.ErrorID = eci3.ErrorID
) as sq
where (
  Cause1 < Cause2
  and Cause2 < Cause3
) or (
  Cause1 < Cause2
  and Cause3 is null
) or (
  Cause2 is null
  and Cause3 is null
) or (
  Cause1 is null
  and Cause2 is null
  and Cause3 is null
)

Finally, we need a correlated subquery to select, for each error, the one row with the highest number of causes (the rest of the rows are simply different permutations of the same causes):

select
  ErrorID
, Cause1
, Cause2
, Cause3
from qryTotalCause as tc1
where tc1.TotalCause = (
  select max(tc2.TotalCause)
  from qryTotalCause as tc2
  where tc1.ErrorID = tc2.ErrorID
)

Simple! (Not :-)

Upvotes: 1

Related Questions