Vitor Neto Ответов: 1

Присоединяйтесь и восстанавливайте уникальные записи

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


Что я уже пробовал:

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

1 Ответов

Рейтинг:
8

Richard Deeming

Похоже, тебе нужна помощь. 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
NB: Вы должны переместить фильтры года из WHERE оговорка к договору LEFT JOIN условия.

Визуальное представление SQL-соединений[^]

Vitor Neto

можете ли вы показать мне, как это сделать, поскольку я новичок в этом деле??

Richard Deeming

У меня просто есть! :)

Замените его FROM и WHERE предложение из вашего запроса с одним из моего ответа.

Vitor Neto

duhhhhhhh к сожалению не видел, что.... извинения

Maciej Los

5ed!

Vitor Neto

Привет Ричард

Просто вернулся к столу и попробовал, и это сработало фантастически.
Большое вам спасибо за ваше время и терпение.

Очень ценю.