Solved

Select values from two tables logic help

Posted on 2011-03-14
11
347 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:jirdeaid
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 5

Expert Comment

by:mayankagarwal
ID: 35127377
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
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127382
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
 
LVL 7

Expert Comment

by:mkobrin
ID: 35127386
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 18

Expert Comment

by:Richard Lee
ID: 35127414
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
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127419
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
 
LVL 18

Expert Comment

by:Richard Lee
ID: 35127493
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
 

Author Comment

by:jirdeaid
ID: 35127613
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
 
LVL 5

Accepted Solution

by:
mayankagarwal earned 500 total points
ID: 35127636
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
 
LVL 7

Expert Comment

by:mkobrin
ID: 35127680
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
 
LVL 9

Expert Comment

by:mayank_joshi
ID: 35127685
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
 

Author Comment

by:jirdeaid
ID: 35127717
Thanks all, mayankagarwal hit the nail on the head.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question