Solved

Select values from two tables logic help

Posted on 2011-03-14
11
346 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

752 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