Display all - when no data link 1

[Darrylles]

This is regarding the return of order line issues per user in a query.

tblUser owns many tblLines, some of these order lines will have issues which are stored in tblLine_Issue (via tblLine & tblIssue_Type keys).
Line issue types are stored in tblIssue_Type (14 possibilities).

So, a user may have 0 or many orders, each of which may have 0 or many issues, which are stored in tblLine_Issue which are linked to tblIssue_Type.
Here are the tables / relationships:
[tt]
tblUser tblLine tblLine_Issue tblIssue_Type
pk <-| pk <-->> line_fk |-->pk
name | Line_No issue_fk <<--| issue
|->> user_fk
[/tt]
I have a requirement to retrieve a count of lines with types of issues for each user, and it must display ALL issue-types (even if the user has zero order lines with that issue).

Example output would be:[tt]
User Issue Type # Issues
1 1 0
1 2 0
1 3 2
1 : :
1 14 0
2 1 3
2 2 2
2 3 0
2 : :
2 14 5
[/tt](The colon ' : ' indicates issue types 4..13).

So, there needs to be 14 rows (14 issue types) per user regardless of whether or not they own order-lines that have that issue.
I think that some kind of union with tblIssue_Type is required - but ...

I know that this to a SQL guru is bread & butter, but despite my best efforts - I'm stuck.

Any help much appreciated.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[r937]

SELECT tblUser.pk          AS user
     , tblIssue_Type.pk    AS issue
     , COUNT(issues.issue) AS number_of_issues
  FROM tblUser
CROSS
  JOIN tblIssue_Type
LEFT OUTER
  JOIN ( SELECT tblLine.user_fk    
              , tblLine_Issue.issue_fk 
           FROM tblLine
         INNER
           JOIN tblLine_Issue
             ON tblLine_Issue.line_fk = tblLine.pk      
       ) AS issues
    ON issues.user_fk = tblUser.pk
   AND issues.issue_fk = tblIssue_Type.pk
GROUP
    BY tblUser.pk   
     , tblIssue_Type.pk

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Darrylles]

Hi r937,

Thank you for your response.

However, CROSS does not seem to be supported in MS Access 2003 (and isn't explicitly represented in 2007).

It seems, that to replicate the 'CROSS' functionality, there needs to be 2 or more tables or sub-queries WITHOUT and explicit join between them.
The 'same-name' fields will then be joined across ALL rows of BOTH tables or queries (I assume that this is your intention with 'CROSS').

Any further ideas?

Thanks.

Darrylle






Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[r937]

stupid Access!!! :-(

try replacing the words CROSS JOIN with a comma...

  FROM ( tblUser
       , tblIssue_Type )
LEFT OUTER
  JOIN ( SELECT ...

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Darrylles]

Hi r,

Getting 'syntax error in join operation' - highlighting tblUser.pk (immediately following the initial SELECT).

You obviously know SQL, and I didn't expect a coded solution - just a 'conceptual' view of how to resolve it.

I'm quite prepared to do the 'leg-work'.

E.g. Produce data-set A, data-set B, non-match join with data-set C (etc).

If I get a generalized description of the solution - I may not end up with an 'optimal' SQL solution, but I will be better equipped to visualize solutions in future.

Thanks for your interest so-far.

ATB

Darrylle



Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[r937]

create a query like this --

SELECT tblUser.pk AS user
     , tblIssue_Type.pk AS issue
  FROM tblUser
     , tblIssue_Type

save this query as "xjoin" and then try this --

SELECT xjoin.user
     , xjoin.issue
     , COUNT(issues.issue) AS number_of_issues  
  FROM xjoin
LEFT OUTER
  JOIN ( SELECT tblLine.user_fk 
              , tblLine_Issue.issue_fk
           FROM tblLine
         INNER
           JOIN tblLine_Issue
             ON tblLine_Issue.line_fk = tblLine.pk
       ) AS issues
    ON issues.user_fk = tblUser.pk
   AND issues.issue_fk = tblIssue_Type.pk
GROUP
    BY xjoin.user
     , xjoin.issue

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Darrylles]

r,

Apologies, but I transposed the actual table names with more 'meaningful' names in order to make the question easier to understand, and from which I hoped to map the suggested solution names to the actual table names.

Problem is: I'm getting confused with the transposition at my end.

The tables that I originally supplied are shown directly above the 'real' table names / field names:
[tt]
tblUser tblLine tblLine_Issue tblIssue_Type

tblTM tblLine tblLP tblPT
pk<-| pk <--------->>line_fk |->pk
|->> teammember_fk pri_type_fk <<-| priority_type

tblTM = Team Member
tblLP = Line Priority
tblPT = Priority Type
[/tt]
Only the keys are important of course, so that's whats I've listed and they are the only required output at this stage.

If you have the heart left to try again - thank you, if not - fully understood.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[r937]

Problem is: I'm getting confused with the transposition at my end.

don't give up, you'll get it



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Darrylles]

r,

Understood mate.

ATB

Darrylle

Never argue with an idiot, he'll bring you down to his level - then beat you with experience.