加入并检索独特的记录 [英] Join and retreive unique records

问题描述

i will try to explain in more details what i have.

i have a table of users which contains

profile_id profile_fname profile_lname

each user needs to fill in an appraisal every year, so when they submit the forms below, the field year will have (1 or 2 or 3), these are the 5 tables


tb1.lesson_observation
tb1.field1
tb1.field2
tb1.field3.profile_id
tb1.year

tb2.self_assess
tb2.field1
tb2.field2
tb2.field3.profile_id
tb2.year

tb3.staffappraisal_head_dept
tb3.field1
tb3.field2
tb3.field3.profile_id
tb3.year

tb4.staffappraisal_main_meeting
tb4.field1
tb4.field2
tb4.field3.profile_id
tb4.year

tb5.staffappraisal_mainapprs_followup
tb5.field1
tb5.field2
tb5.field3.profile_id
tb5.year

I would like to achieve an output list that shows me what every unique user has done so far for the present year (ie:2), if they have filled in the relevant form if not to show a null.


User ID    | Fname| Lname | TB1 Header | TB2 Header | TB3 Header | TB4 Head etc
----------------------------------------------------------------------
33         | john | wayne | 2          | null       | 2          |  2 
25         | Mark | yew   | 2          | null       | 2          |  2 
22         | bell | dow   | 2          | null       | null       |  2 
18         | pete | nell  | 2          | null       | 2          |  2 
11         | lee  | drei  | 2          | null       | 2          |  null 

我的尝试：

What I have tried:

SELECT
dbo.profiles.profile_id,
dbo.self_assess.sa_year,
dbo.self_assess.self_assess_profile_id,
dbo.lesson_observation.lo_year,
dbo.lesson_observation.lesson_obsv_profile_id,
dbo.staffappraisal_head_dept.sahd_year,
dbo.staffappraisal_head_dept.sa_hd_profile_id,
dbo.staffappraisal_main_meeting.samm_year,
dbo.staffappraisal_main_meeting.sa_mainappraisal_teacherid,
dbo.staffappraisal_mainapprs_followup.sa_ma_fup_teachers_id,
dbo.staffappraisal_mainapprs_followup.samafu_year

FROM
dbo.profiles
INNER JOIN dbo.self_assess ON dbo.profiles.profile_id = dbo.self_assess.self_assess_profile_id
INNER JOIN dbo.lesson_observation ON dbo.profiles.profile_id = dbo.lesson_observation.lesson_obsv_profile_id
INNER JOIN dbo.staffappraisal_head_dept ON dbo.profiles.profile_id = dbo.staffappraisal_head_dept.sa_hd_profile_id
INNER JOIN dbo.staffappraisal_main_meeting ON dbo.profiles.profile_id = dbo.staffappraisal_main_meeting.sa_mainappraisal_teacherid
INNER JOIN dbo.staffappraisal_mainapprs_followup ON dbo.profiles.profile_id = dbo.staffappraisal_mainapprs_followup.sa_ma_fup_teachers_id
WHERE
dbo.self_assess.sa_year = 2 AND
dbo.lesson_observation.lo_year = 2 AND
dbo.staffappraisal_head_dept.sahd_year = 2 AND
dbo.staffappraisal_main_meeting.samm_year = 2 AND
dbo.staffappraisal_mainapprs_followup.samafu_year = 2

推荐答案

看起来你需要 LEFT JOIN ：

Looks like you need a LEFT JOIN:

FROM
    dbo.profiles
    LEFT JOIN dbo.self_assess ON dbo.profiles.profile_id = dbo.self_assess.self_assess_profile_id And dbo.self_assess.sa_year = 2
    LEFT JOIN dbo.lesson_observation ON dbo.profiles.profile_id = dbo.lesson_observation.lesson_obsv_profile_id And dbo.lesson_observation.lo_year = 2
    LEFT JOIN dbo.staffappraisal_head_dept ON dbo.profiles.profile_id = dbo.staffappraisal_head_dept.sa_hd_profile_id And dbo.staffappraisal_head_dept.sahd_year = 2
    LEFT JOIN dbo.staffappraisal_main_meeting ON dbo.profiles.profile_id = dbo.staffappraisal_main_meeting.sa_mainappraisal_teacherid And dbo.staffappraisal_main_meeting.samm_year = 2
    LEFT JOIN dbo.staffappraisal_mainapprs_followup ON dbo.profiles.profile_id = dbo.staffappraisal_mainapprs_followup.sa_ma_fup_teachers_id And dbo.staffappraisal_mainapprs_followup.samafu_year = 2

注意：您必须从 WHERE中移动年份过滤器子句到 LEFT JOIN 条件。



SQL连接的可视化表示 [ ^ ]

NB: You have to move the year filters from the WHERE clause to the LEFT JOIN conditions.

Visual Representation of SQL Joins[^]

这篇关于加入并检索独特的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！