Select values from two tables logic help
Posted on 2011-03-14
I have searched for a solution before posting, couldnt find a conclusive answer so i am posting for help. Do apologise me if this question has already been answered and i missed it.
I have two tables with the following design:
CID(int,identity,pk) ConceptName(str) ConceptDescription(str)
1 Concept1 this is concept one description
2 Concept2 this is concept two description
_recID(int,identity,pk) CID(int) ConceptSynonymID(int)
1 2 23
2 2 11
So, table 1 contains all the concepts, table two is a relation table between concepts, that reflects their synonyms. So, on the above example, Concept2 Has two Synonyms, concept23 and concept11
this is how far i've gotten with what i want:
SELECT VF_ConceptInfo.CID, VF_ConceptInfo.ConceptName, VF_ConceptSynonymInfo.ConceptSynonymID
FROM VF_ConceptSynonymInfo INNER JOIN
VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID
and the resulting table is:
CID ConceptName ConceptSynonymID
2 Concept2 11
2 concept2 23
What i really wanted it to display is the following table:
CID ConceptName ConceptSynonymID "SynonymName"
2 Concept2 11 concept11
2 concept2 23 concept23
I am lost on how to acomplish this, stuck in a logic loophole i cant solve.
Is this Database design problem?
Thanks in advance