Thought distinct keyword is the solution, but really it is not....

Question

Thought distinct keyword is the solution, but really it is not....

rajan416

SSC Enthusiast

Points: 120
More actions
March 9, 2009 at 4:57 am

#95106

I have 2 tables with parent child relationship. i need to take parent tables values and need to combine with child table values using foreign key relationship.
table 1
nameidnameagecontact#
1alex2412346
2john26111111
3ferdi2622222
table 2
gameidnameidgame
13cricket
23football
32base ball
42volley ball
51tennis
61rugby
now i need o/p like this
nameidnameagecontact#nameidgame
1alex24123461rugby
2john261111112base ball
3ferdi26222223cricket
i need only one child should be linked with parent table ,i.e one game should be mapped with one person.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

j nuñez SSCrazy Points: 2175 More actions · Answer 1

First of all, can you put the DDL of the tables, and some sentences with the inserts of the dato to simplify for us the task of help you?

There are a lot of articles in this web, about the way to ask help, and how to put a message/problem.

And second, is there any rule to select one register or another in the child table?

j nuñez SSCrazy Points: 2175 More actions · Answer 2

Excuse me, i forgot to put a link to an article with an example of how to ask help.

Here is:

How to post data/code on a forum to get the best help

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

You have to inlcude a mechanism for eliminating duplicate games. Is it the first one encountered? The first one alphabetically? The one with the lowest or highest ID number? You can't just arbitrarilly say that you only want one without supplying the mechanism to get to that one.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Mando2 SSChasing Mays Points: 609 More actions · Answer 4

First of all, I completely concur with the above posts - you may want to provide more clarity.

Having said that, this is my attempt to help you keep moving -

You could do a co-related sub-query like this:

SELECT

nameid,

name,

age,

contact#,

(SELECT TOP 1 t2.game FROM Table2 as t2 WHERE t2.nameId = t1.nameId)

FROM Table1 as t1

NOTE: TOP 1 will return a "random" game from Table2 for a given nameId. And this is where your problem definition is unclear.

HTH.