locked
conditional relationship. RRS feed

  • Question

  • Hello,

    Is it possible to create a relationship based on a condition?

    For e.g In my database i have 3 tables

    Create table Login_tbl
    (Login_ID (some data type),
    Password (some data type) not null,
    Is_Student char(1) check (Is_Student='Y' or Is_Student='N') not null
    )
    
    
    Create table Student_tbl 
    (
    Name....,
    Gender...,
    Login_ID (some data type) 
    )
    
    
    Create table Teacher_tbl
    (
    Name......,
    Gender......,
    Login_ID (some data type) 
    )

    Question. I want to relate the field Login_ID (Login_tbl) to Student_tbl IF Is_Student ='Y' AND to Teacher_tbl IF Is_Student ='N'

    So is this possible and how?

    Monday, July 1, 2013 3:15 PM

Answers

  • If "Login_ID" in Login Table is an incremental value as well as primary key, You do not need to specify a condition to relate your student and teacher table. Assuming that Login_ID in Student and Teacher table are Foreign Keys. You can try the following query,

    SELECT * FROM (
    	SELECT NAME,GENDER,LOGIN_ID,'STUDENT' AS LOGINTYPE FROM STUDENT_TBL
    	UNION
    	SELECT NAME,GENDER,LOGIN_ID,'TEACHER' AS LOGINTYPE FROM TEACHER_TBL
    ) X INNER JOIN LOGIN_TBL Y ON
    X.LOGIN_ID=Y.LOGIN_ID


    Regards, RSingh

    • Proposed as answer by Sofiya Li Wednesday, July 3, 2013 10:13 AM
    • Marked as answer by Sofiya Li Tuesday, July 23, 2013 1:17 AM
    Monday, July 1, 2013 3:23 PM
    Answerer
  • Another alternative can be,

    SELECT * FROM LOGIN_TBL A INNER JOIN STUDENT_TBL B ON A.LOGIN_ID=B.LOGIN_ID
    WHERE A.IS_STUDENT = 'Y'
    UNION
    SELECT * FROM LOGIN_TBL A INNER JOIN TEACHER_TBL ON A.LOGIN_ID=B.LOGIN_ID
    WHERE IS_STUDENT = 'N'


    Regards, RSingh

    • Proposed as answer by Sofiya Li Wednesday, July 3, 2013 10:13 AM
    • Marked as answer by Sofiya Li Tuesday, July 23, 2013 1:17 AM
    Monday, July 1, 2013 3:32 PM
    Answerer

All replies

  • If "Login_ID" in Login Table is an incremental value as well as primary key, You do not need to specify a condition to relate your student and teacher table. Assuming that Login_ID in Student and Teacher table are Foreign Keys. You can try the following query,

    SELECT * FROM (
    	SELECT NAME,GENDER,LOGIN_ID,'STUDENT' AS LOGINTYPE FROM STUDENT_TBL
    	UNION
    	SELECT NAME,GENDER,LOGIN_ID,'TEACHER' AS LOGINTYPE FROM TEACHER_TBL
    ) X INNER JOIN LOGIN_TBL Y ON
    X.LOGIN_ID=Y.LOGIN_ID


    Regards, RSingh

    • Proposed as answer by Sofiya Li Wednesday, July 3, 2013 10:13 AM
    • Marked as answer by Sofiya Li Tuesday, July 23, 2013 1:17 AM
    Monday, July 1, 2013 3:23 PM
    Answerer
  • Another alternative can be,

    SELECT * FROM LOGIN_TBL A INNER JOIN STUDENT_TBL B ON A.LOGIN_ID=B.LOGIN_ID
    WHERE A.IS_STUDENT = 'Y'
    UNION
    SELECT * FROM LOGIN_TBL A INNER JOIN TEACHER_TBL ON A.LOGIN_ID=B.LOGIN_ID
    WHERE IS_STUDENT = 'N'


    Regards, RSingh

    • Proposed as answer by Sofiya Li Wednesday, July 3, 2013 10:13 AM
    • Marked as answer by Sofiya Li Tuesday, July 23, 2013 1:17 AM
    Monday, July 1, 2013 3:32 PM
    Answerer
  • This is Super type-subtype entities. What you may do is,  The login table (I don't agree with this naming convention it could have been a personnel table or something ) add a type column as discriminator.  in the two subtype tables Student and teacher the PK should be the FK from Login ( Identifying relationship) ie. ONE TO ONE relation.  All specific columns in subtypes and all generic columns in Super type. WHen you do physical modeling you can do a rollup or rolldown based on the requirement.

    thanks

    madhu


    MCITP, MCTS, MCDBA,MCP

    Monday, July 1, 2013 3:34 PM
  • No, you can't do that.  Your foreign key should be used to relate a row in Student_tbl to a row in login_tbl, not the other way around. 

    Monday, July 1, 2013 5:36 PM
  • Sir, could you explain this to me i simple terms

    Monday, July 1, 2013 6:25 PM
  • Please read about super type subtype entities. There are many resources available in net. Whether you can create FK like you mentioned does not raise at all. the issue is your design.  You dont need the flag columns.

    Some how i am not able to copy paste the links... Please do a bing/google search for Super type subtype entities.

    

    thanks

    Madhu


    MCITP, MCTS, MCDBA,MCP



    • Edited by Madhu K Nair Monday, July 1, 2013 7:08 PM added link
    Monday, July 1, 2013 6:58 PM
  • I think the first thing you need to consider is whether [Is_Student] is an Attribute of the Entity [LogIn]... and consequently, whether this Field should be included in the Table Login_tbl.

    AFAIK, this is an Attribute of the involved Person and not a direct Attribute on the Entity [LogIn].

    [Person] as the super-type Entity with [Student] and [Teacher] as sub-type Entities is probably close to the structure/set-up that Madhu suggested.


    Van Dinh


    • Edited by Van Dinh Tuesday, July 2, 2013 12:11 AM Clarification
    Tuesday, July 2, 2013 12:09 AM
  • Use case expression

    SELECT a.au_lname, a.au_fname, a.address,
     t.title, t.type
     FROM authors a INNER JOIN
     titleauthor ta ON ta.au_id = a.au_id INNER JOIN
     titles t ON t.title_id = ta.title_id
    INNER JOIN publishers p on t.pub_id =
        CASE WHEN t.type = 'Business' THEN p.pub_id  ELSE null END
    INNER JOIN stores s on s.stor_id  =
        CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Tuesday, July 2, 2013 5:22 AM
  • Do you want to create a foreign key relationship? AFAIK, you cannot enforce this rule by creating a foreign key constraint. However if you wish you could ensure the data integrity by using triggers.

    Satheesh

    Tuesday, July 2, 2013 5:30 AM
  • Tuesday, July 2, 2013 5:31 AM