how get only 1 record from 2nd table when joining

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

how get only 1 record from 2nd table when joining

Author

Topic

mbwittig
Starting Member

1 Post

Posted - 2010-08-13 : 12:39:19

Hello,

I have a table of words and a table of definitions. the table of
definitions can have multiple records per word. I would like to get
the record with the lowest ID from the defs table for each word and
join them so I have id, word, def

when I try this it doesn't work right, I only get the same definition
for all the words

select id, word, def = (select top 1 def from defs where word_id = id)
FROM words

words table has id, word
defs table has word_id, def

any help would be appreciated!

Mike

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-13 : 13:29:57

I'm finding your data model confusing (or the description of what you want out of it). Perhaps some sample data and expected output would help. Here is a link that can help you prepare that information so we can run queries against it:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Humate
Posting Yak Master

101 Posts

Posted - 2010-08-13 : 17:51:50

like this?

SELECT w.id, w.word, z.def
FROM words w
INNER JOIN 
			(SELECT * 
			 FROM (SELECT word_id, def, ROW_NUMBER() OVER(PARTITION BY word_id ORDER BY word_id) AS RecID
				   FROM defs) AS d
			 WHERE RecID = 1) AS z ON w.id = z.word_id

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-14 : 01:28:53

[code]select w.id, w.word, d.def
FROM words w
CROSS APPLY (select top 1 def
from defs
where word_id = w.id
order by id asc)d
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources