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

[cpietri]

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.

[GolferGuy]

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.

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:

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")