Select values from two tables logic help

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

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

Select allOpen in new window

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

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

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

Select allOpen in new window

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

Select allOpen in new window

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

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

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

Select allOpen in new window

Thanks all, mayankagarwal hit the nail on the head.