0

Background:

Hi there, first time posting a question so please excuse any wrongdoings... I have been given an assignment at university using ASP.NET and SQL Server, what we basically have to do is create a webform where we can select a political party via a radio button list bound to a SQL data source. Based on this selection, a drop-down list is then populated with a list of MPs who are a part of the party selected the radio button list. You can then select an MP and view his or her personal details, their roles in parliament and ask/view questions via a number of data controls linked to various SQL data sources.

I'm making good progress and trying to use whatever resources I can find to get solutions but alas I've hit a wall with viewing the parliamentary roles after selecting the MP.

I have 3 tables which are involved here:

MP                   MP_ROLES               ROLE
-------------        ---------------        ----------------
MP_NUM (PK)          MP_NUM (PK, FK)        ROLE_NUM (PK)
MP_FIRST             ROLE_1 (FK)            ROLE_DESCRIPTION
MP_LAST              ROLE_2 (FK)
MP_EMAIL             ROLE_3 (FK)
MP_CONTACTADD        ROLE_4 (FK)
MP_PHOTO
PARTY_NUM(FK)

(where ROLE_1 through ROLE_4 reference ROLE.ROLE_NUM.)

Problem:

I have a DetailsView control which is bound to an SQL data source which queries MP_ROLES. What I'm basically trying to do is replace each value in ROLE_1, ROLE_2, etc. with the corresponding ROLE_DESCRIPTION value referenced by ROLE_NUM in the ROLE table (as MP_ROLE.ROLE_1 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_2 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_3 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_4 = ROLE.ROLE_NUM). Now through my digging through textboxes I know in more simple applications you can do what I'm trying to do with the INNER JOIN function, however as I'm still learning I haven't quite got the syntax sorted to do what I want to do.

What I have so far:

SELECT MP_ROLES.MP_NUM, MP_ROLES.ROLE_1, MP_ROLES.ROLE_2, MP_ROLES.ROLE_3, MP_ROLES.ROLE_4
FROM MP_ROLES 
INNER JOIN ROLE ON MP_ROLES.ROLE_1 = ROLE.ROLE_NUM 
INNER JOIN ROLE AS ROLE_3 ON MP_ROLES.ROLE_2 = ROLE.ROLE_NUM 
INNER JOIN ROLE AS ROLE_2 ON MP_ROLES.ROLE_3 = ROLE.ROLE_NUM 
INNER JOIN ROLE AS ROLE_1 ON MP_ROLES.ROLE_4 = ROLE.ROLE_NUM 
WHERE (MP_ROLES.MP_NUM = @MP_NUM)

I've been hung up on this for the last 8 trying to find a solution so if there is anyone who is able show me where I'm going wrong or perhaps kind enough to provide a solution it would be very much appreciated :)

stakx - no longer contributing
  • 83,039
  • 20
  • 168
  • 268
Muz
  • 39
  • 6

2 Answers2

0

Your problem lies in the = ROLE.ROLE_NUM. You have to specify the alias here too. Also I rearranged some numbers since your numbers were all mixed up.

SELECT MP_ROLES.MP_NUM
, MP_ROLES.ROLE_1
, MP_ROLES.ROLE_2
, MP_ROLES.ROLE_3
, MP_ROLES.ROLE_4 

FROM MP_ROLES

INNER JOIN ROLE AS ROLE_1 ON MP_ROLES.ROLE_1 = ROLE_1.ROLE_NUM
INNER JOIN ROLE AS ROLE_2 ON MP_ROLES.ROLE_2 = ROLE_2.ROLE_NUM
INNER JOIN ROLE AS ROLE_3 ON MP_ROLES.ROLE_3 = ROLE_3.ROLE_NUM
INNER JOIN ROLE AS ROLE_4 ON MP_ROLES.ROLE_4 = ROLE_4.ROLE_NUM

WHERE (MP_ROLES.MP_NUM = @MP_NUM)
Wietze314
  • 5,942
  • 2
  • 21
  • 40
  • Hi Wietze314, thank you for your solution. Is it possible from here to retrieve the relevant ROLE.ROLE_DESCRIPTION value which is referenced by ROLE.ROLE_NUM? This is the ultimate goal. thanks, Murray – Muz Oct 26 '13 at 02:24
  • 1
    @muzzcuzz92: In the `SELECT` clause, just replace e.g. `MP_ROLES.ROLE_2` with `ROLE_2.ROLE_DESCRIPTION`. – stakx - no longer contributing Oct 27 '13 at 06:24
0

It looks as though what I was actually aiming for can be done using the LEFT OUTER JOIN function:

SELECT 
MP_ROLES.MP_NUM, 
MP_ROLES.ROLE_1, 
MP_ROLES.ROLE_2, 
MP_ROLES.ROLE_3, 
MP_ROLES.ROLE_4, 
ROLE1.ROLE_NUM, ROLE1.ROLE_DESCRIPTION AS POSITION1, 
ROLE2.ROLE_NUM, ROLE2.ROLE_DESCRIPTION AS POSITION2, 
ROLE3.ROLE_NUM, ROLE3.ROLE_DESCRIPTION AS POSITION3, 
ROLE4.ROLE_NUM, ROLE4.ROLE_DESCRIPTION AS POSITION4 

FROM MP_ROLES 
LEFT OUTER JOIN ROLE AS ROLE1 ON MP_ROLES.ROLE_1 = ROLE1.ROLE_NUM 
LEFT OUTER JOIN ROLE AS ROLE2 ON MP_ROLES.ROLE_2 = ROLE1.ROLE_NUM 
LEFT OUTER JOIN ROLE AS ROLE3 ON MP_ROLES.ROLE_3 = ROLE1.ROLE_NUM 
LEFT OUTER JOIN ROLE AS ROLE4 ON MP_ROLES.ROLE_4 = ROLE1.ROLE_NUM 

WHERE (MP_ROLES.MP_NUM = @MP_NUM)
Muz
  • 39
  • 6