Databases
StackOverflow2013
Postsdbo
POSQL - How to link two tables with a third table and get all records
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL - How to link two tables with a third table and get all records
    primarykey
    data
    text
    <p>Okay, so these are my three tables:</p> <p><strong>USER</strong></p> <pre><code>|--------+----------+----------| | UserID | UserName | IsActive | |--------+----------+----------| | 10 | Mike | 1 | | 11 | John | 1 | | 12 | Beth | 1 | |--------+----------+----------| </code></pre> <p><strong>REPORT_DISTRIB</strong> (Linking Table)</p> <pre><code>|-----------+--------+----------+---------------| | DistribID | UserID | ReportID | DistribToUser | |-----------+--------+----------+---------------| | 1 | 10 | 50 | 1 | | 2 | 12 | 52 | 0 | | 3 | 14 | 54 | 1 | |-----------+--------+----------+---------------| </code></pre> <p><strong>REPORT</strong></p> <pre><code>|----------+------------+---------------| | ReportID | ReportName | Distributable | |----------+------------+---------------| | 50 | FY2010 | 1 | | 51 | FY2011 | 1 | | 52 | FY2012 | 1 | |----------+------------+---------------| </code></pre> <p>In the problem I'm facing, I have 200 users from the USER table that are active and 10 reports from the REPORT table that are distributable. For every user I need to display the User Name, the Report Name, and whether that report should be distributed to the user, like so:</p> <pre><code>|----------+------------+---------------| | UserName | ReportName | DistribToUser | |----------+------------+---------------| | Mike | FY2010 | 1 | | Beth | FY2012 | 0 | |----------+------------+---------------| </code></pre> <p>What I'm trying to achieve is a list of 2000 results (200 users x 10 reports). The problem is that the REPORT_DISTRIB linking table doesn't have a record for every user with each report. I still feel like this should be possible... am I wrong in my thinking? Any help is greatly appreciated.</p> <p>It's rough, but this is my query so far (which returns 1790 results):</p> <pre><code>SELECT u.UserName, r.ReportName, rd.DistribToUser FROM USER u LEFT JOIN REPORT_DISTRIB rd on rd.UserID = u.UserID and rd.ReportID in (select r.ReportID from REPORT r where r.Distributable = 1) OUTER APPLY (select r.ReportName from REPORT r where r.ReportID = rd.ReportID) r WHERE u.IsActive = 1 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USMark Smithhart
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL - How to link two tables with a third table and get all records
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSo to be I correctly understand you want to return 10 rows for each 200 users, 1 row for each report for each user. In the case where that user has a linking record with that report put a 1 in the other case put a 0?
      singulars
      1. POSQL - How to link two tables with a third table and get all records
      1. USJordan
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload