Results 1 to 2 of 2

Thread: problem with basic junction in query: not be able to retreive the proper data

  1. #1
    Join Date
    May 2012
    Posts
    3

    problem with basic junction in query: not be able to retreive the proper data

    I have the following tables:

    Person (PK_PersonID, PersonFullName, FK_MbtiID)
    Mbti(PK_MbtiID)
    Dimension(PK_DimensionID, DimensionName)
    MbtiDimension(FK_PK_DimensionID, FK_PK_MbtiID)

    Dimension has data as followed:

    PK_DimensionID, DimensionName
    ---------------------------------------------
    E, Extraversion
    I, Introversion
    S, Sensing
    N, Intuition
    T, Thinking
    F, Feeling
    J, Judging
    P, Perception

    there are 16 mbti types from these dimensions, so the table Mbti has data as following:

    PK_MbtiID
    --------------
    ISTJ
    ISFJ
    INFJ
    INTJ
    ISTP
    ISFP
    INFP
    INTP
    ESTP
    ESFP
    etc...

    The link (entity relation many to many) between Mbti and Dimension is MbtiDimension which has data as following:

    FK_PK_MbtiID, FK_PK_DimensionID
    -------------------------------------------------
    ISTJ, I
    ISTJ, S
    ISTJ, T
    ISTJ, J
    ISFJ, I
    ISFJ, S
    ISFJ, F
    ISFJ, J
    INFJ, I
    INFJ, N
    INFJ, F
    INFJ, J
    etc...

    Person has one personnality,

    PK_PersonID, PersonName, FK_MbtiID
    ---------------------------------------------------
    1, John Citizen, ISTJ
    2, Patrick Dupont, ISFJ
    etc...


    I want to retreive the John Citizen given the parameter I, S, T.

    I am doing the following:

    SELECT personName
    FROM Person P, Mbti M, MbtiDimension md, Dimension d
    WHERE P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Introversion"
    AND DimensionName = "Sensing"
    AND DimensionName = "Thinking"

    It does not return anything. However when I do the following:

    SELECT personName
    FROM Person P, Mbti M, MbtiDimension md, Dimension d
    WHERE P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Introversion"
    OR DimensionName = "Sensing"
    OR DimensionName = "Thinking"

    It returns me 2 lines, I understand why but I do not understand however why I do not have any line with the and command. Am I missing something?

    Could you help me, solving this.

    thank you in advance.

  2. #2
    Join Date
    May 2006
    Posts
    407
    Code:
    SELECT personName
    FROM Person P, Mbti M, MbtiDimension md, Dimension d
    WHERE P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Introversion"
    AND DimensionName = "Sensing"
    AND DimensionName = "Thinking"
    This code will NEVER return any records. You have "DimensionName" being equal to "Introversion" AND equal to "Sensing" AND equal to "Thinking". The field DimensionName can only be equal to ONE thing at a time. It is impossible for it to be equal to all three things within one output record.

    Code:
    WHERE P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Introversion"
    OR DimensionName = "Sensing"
    OR DimensionName = "Thinking"
    You have 4 comparisons that all must be true to select a record. That is because all 4 comparisons have an "AND" between them. You then have two comparisons that if either of them are equal, you will select a record to be included in the result set. These last two comparisons are included in the WHERE clause with an OR, therefore just this one comparison needs to be true to include the record.
    What I am assuming you actually want is for the first three comparisons to be true, along with the 4th to be true, OR the 5th to be true along with the first three, OR the 6th comparison to be true along with the first three to be true.
    But that is not what you will get because of the way the WHERE clause is written.
    The most straight forward way to write the WHERE clause, if I'm right about what you are really wanting, would be this:
    Code:
    WHERE (P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Introversion")
    OR 
    (P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Sensing")
    OR 
    (P.FK_MbtiID = M.PK_PersonID
    AND M.PK_MbtiID = md.FK_PK_MbtiID
    AND md.FK_PK_DimensionID = d. PK_DimensionID
    AND DimensionName = "Thinking")
    Last edited by GolferGuy; 05-15-2012 at 01:10 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •