Solved

Select values from two tables logic help

Posted on 2011-03-14
11
342 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
  • 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now