Results 1 to 6 of 6
  1. #1
    Join Date
    May 2012
    Posts
    2

    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
    Feb 2008
    Location
    Japan
    Posts
    3,483
    (1) Mbti table is not neccesary for the query.
    Though, Mbti table is usefull to prevent garbage FK_PK_MbtiID values mixed in MbtiDimension table by a foreign key constraint.

    (2) First try to find required FK_PK_MbtiID. Then join them with Person table.

    (3) You may want to use relational division technique to find required FK_PK_MbtiID.
    The relational division might be like...
    MbtiDimension(FK_PK_MbtiID, FK_PK_DimensionID)
    divide by
    (SELECT PK_DimensionID FROM Dimension WHERE DimensionName IN ('Introversion' , 'Sensing' , 'Thinking') )

    Please search forums or Books to find how to accmplish relational division by SQL.

    Note: I know basically three techniques of relational division.
    (1) Nesting of two EXISTS predicates
    (2) GROUP BY FK_PK_MbtiID and HAVING COUNT(*) = ...
    (3) LISTAGG(or GROUP_CONCAT), like...
    HAVING LISTAGG(md.FK_PK_MbtiID , ',') ... LIKE (SELECT LISTAGG('%' || PK_DimensionID || '%') ... )
    Last edited by tonkuma; 05-14-12 at 06:21. Reason: Add somemore detail to Note(3). Add column list to psudo code in (3)

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Note(4):
    There may be another technique using signature or checksum of concatenated string of MbtiID for "Exact Division".
    But, "Division with a Remainder" must be neccesary in this time.
    So, this technique may be not applicable.

  4. #4
    Join Date
    May 2012
    Posts
    2

    Query problem, being solved...

    Okay thank you for your answer, I'm gonna check that then!

    I will search for a db book, in the meantime, if you have any interested book concerning db concept do you mind sharing it.

    I deleted the table as you advice me, I will play with the SQL now and as soon as I found the proper query, I'll put it on the forum...

    Cheers.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i like tonkuma's solution (2) with HAVING COUNT(*)
    Code:
    SELECT p.personName
      FROM Person P
    INNER
      JOIN MbtiDimension md
        ON md.FK_PK_MbtiID = P.FK_MbtiID
    INNER
      JOIN Dimension d
        ON d.PK_DimensionID = md.FK_PK_DimensionID
       AND d.DimensionName IN ( 'Introversion'
                              , 'Sensing'
                              , 'Thinking' )
    GROUP
        BY p.personName
    HAVING COUNT(*) = 3
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

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
  •