Select values from two tables logic help

Hello Experts,

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:

Table VF_ConceptInfo
CID(int,identity,pk)      ConceptName(str)       ConceptDescription(str)
1                                 Concept1                     this is concept one description
2                                 Concept2                     this is concept two description

Table VF_ConceptSynonymInfo
_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
João serras-pereiraAsked:
Who is Participating?
 
mayankagarwalCommented:
SELECT        VF_ConceptInfo.CID, VF_ConceptInfo.ConceptName, VF_ConceptSynonymInfo.ConceptSynonymID, (SELECT ConceptName FROM  VF_ConceptInfo C WHERE C.CID = VF_ConceptSynonymInfo.ConceptSynonymID) as SynonymName
FROM            VF_ConceptSynonymInfo INNER JOIN
                         VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID
0
 
mayankagarwalCommented:
where this SynonymName is stored in the db, or if it is a combination of concept and ConceptSynonymID then the solution is:


SELECT        VF_ConceptInfo.CID, VF_ConceptInfo.ConceptName, VF_ConceptSynonymInfo.ConceptSynonymID, CONCAT("concept", VF_ConceptSynonymInfo.ConceptSynonymID) as SynonymName
FROM            VF_ConceptSynonymInfo INNER JOIN
                         VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID
0
 
mayank_joshiCommented:
try:-
select CID, ConceptName, ConceptSynonymID,'concept' + ConceptSynonymID
 as SynonymName from
(SELECT        VF_ConceptInfo.CID, VF_ConceptInfo.ConceptName, VF_ConceptSynonymInfo.ConceptSynonymID
FROM            VF_ConceptSynonymInfo INNER JOIN
                         VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID) derived_table

Open in new window

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
mkobrinCommented:
select ci.CID, ci.ConceptName, csi.ConceptSynonymID
, 'concept' + cast(csi.ConceptSynonymID as varchar(4)) as SynonymName
from Table VF_ConceptInfo  ci
inner join VF_ConceptSynonymInfo csi on csi.CID = ci.CID

0
 
Richard LeeSoftware EnthusiastCommented:
You haven't shown the 3rd table that contains the synonym data however this is the relationship as I understand it.

VF_ConceptInfo  -----------> VF_ConceptSynonymInfo <---------------- VF_SynonymInfo

Your query should look like this:

SELECT
   VF_ConceptInfo.CID,
   VF_ConceptInfo.ConceptName,
   VF_ConceptSynonymInfo.ConceptSynonymID,
   VF_SynonymInfo.SynonymName

FROM  VF_ConceptSynonymInfo
   INNER JOIN VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID
   INNER JOIN VF_SynonymInfo ON VF_ConceptSynonymInfo.ConceptSynonymID = VF_SynonymInfo.SID

DaTribe
0
 
mayank_joshiCommented:
better:-

SELECT        VF_ConceptInfo.CID, VF_ConceptInfo.ConceptName, VF_ConceptSynonymInfo.ConceptSynonymID, 'Concept' + VF_ConceptSynonymInfo.ConceptSynonymID  as SynonymName
FROM            VF_ConceptSynonymInfo INNER JOIN
                         VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID

Open in new window

0
 
Richard LeeSoftware EnthusiastCommented:
Doing some mental SQL here. The above query may need slight restucturing.

DaTribe
SELECT
   VF_ConceptInfo.CID,
   VF_ConceptInfo.ConceptName,
   VF_ConceptSynonymInfo.ConceptSynonymID,
   VF_SynonymInfo.SynonymName

FROM VF_ConceptInfo 
   INNER JOIN VF_ConceptSynonymInfo ON VF_ConceptInfo.CID = VF_ConceptSynonymInfo.CID
   INNER JOIN VF_SynonymInfo ON VF_ConceptSynonymInfo.ConceptSynonymID = VF_SynonymInfo.SID

Open in new window

0
 
João serras-pereiraAuthor Commented:
Not quite there yet.

@ mayankagarwal -  im getting a CONCAT error when runing your query - 'CONCAT' is not a recognized built-in function name.

Im using VS 2010 query builder, and SQL server 2008.

@ mayank_joshi - Conversion failed when converting the varchar value 'concept' to data type int.

@ mkobrin  -  query runs great, and its really close to what i intend to have, but it might be my database design problem, or my explanation that wasnt quite .. well.. explanatory.

@ DaTribe - From seeing your reply, im "concluding" it really might be my design, cause what i dont have a 3rd table.

 Let me try and put some real data onscreen:

Table VF_ConceptInfo
CID(int,identity,pk)      ConceptName(str)       ConceptDescription(str)
1                                 Test                             Description
2                                 Soccer                         Description
3                                 Ocean                          Description
4                                 Sea                              Description
5                                 Quizz                           Description
6                                 Football                         Description
7                                 Exam                           Description

Table VF_ConceptSynonymInfo
_recID(int,identity,pk)   CID(int)                       ConceptSynonymID(int)
1                                   1                                  5
2                                   1                                  7
3                                   2                                  6
4                                   3                                  4

What i wanted it to display is the following table:

CID           ConceptName        ConceptSynonymID     "SynonymName"
1               Test                        5                                    Quizz
2               Test                        7                                    Exam

etc..

Do i need a SynInfo table with the synonim names? cause the sunonyms will be included in the concept table as well, as  they're concepts as well

Thanks for all the answers so far





0
 
mkobrinCommented:
select ci.CID, ci.ConceptName, csi.ConceptSynonymID, (select ConceptName from VF_ConceptInfo  where CID = csi.CID) as SynonymName
from Table VF_ConceptInfo  ci
inner join VF_ConceptSynonymInfo csi on csi.CID = ci.CID

0
 
mayank_joshiCommented:
try this:-

SELECT        VF_ConceptInfo.CID, VF_ConceptInfo.ConceptName, VF_ConceptSynonymInfo.ConceptSynonymID, 'Concept' +cast(VF_ConceptSynonymInfo.ConceptSynonymID as NVARCHAR(100))  as SynonymName
FROM            VF_ConceptSynonymInfo INNER JOIN
                         VF_ConceptInfo ON VF_ConceptSynonymInfo.CID = VF_ConceptInfo.CID

Open in new window

0
 
João serras-pereiraAuthor Commented:
Thanks all, mayankagarwal hit the nail on the head.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.