only get the forst match from table 2

Question

only get the forst match from table 2

OJDev

SSC Veteran

Points: 248
More actions
January 4, 2010 at 2:50 pm

#219063

I have 2 tables with table 1 containing columns m_ID(int), description and table 2 containing m_ID(int), v_ID.
I'm joining the 2 tables :
SELECT t1.m_ID ,
t1.description,
t2.v_ID
FROM table1 t1
INNER JOIN table2 t2
ON t2.m_ID = t1.m_ID
Table2 has multiple entries for m_ID whereas it is the unique key in table1. What I would like to have as a result is to return all entries from table one and its first match from table 2.
Any help is appreciated.

Viewing 10 posts - 1 through 10 (of 10 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How would you define 'first match'? Is there a date column in your table2 that would tell us which row comes first? If not, is there an incrementing column of any sort?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Agree I do with John.

For better answers to your question, read you might the first article referenced below I have regarding asking for assistance.

Table structures, sample data, expected results would benefit greatly. Tested code in return you will get.

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

Jump on the bandwagon with Lynn and John will I. If you provide the information requested by John and Lynn, we can more quickly provide suggestions/answers.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OJDev SSC Veteran Points: 248 More actions · Answer 4

table 1 has the columns :

m_ID : data type integer primary unique key auto increment and

description: data type nvarchar

table 2 has the columns:

m_id: data type integer no key, no auto increment and

v_ID: nvarchar

for example

table 1

1001 | TextText

1002 | TextText2

1003 | TextTextMore

table 2

1001 | 3569

1001 |

1001 | 3445

1002 | 6969

1002 |

1003 | 1234

1003 | 1365

The result set I would like to have returned is :

1001|TextText|3569

1002|TextText2|6969

1003|TextTextMore|1234

so only the first match it finds in the table2.

Does this make more sense?

Thanks for your help.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Article you really should read. The more you do for us, the more we will do for you.

John Rowan SSC Guru Points: 56440 More actions · Answer 6

What happens when Table2 does not have a non-NULL value for v_ID, then what do you want to see?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

John Rowan SSC Guru Points: 56440 More actions · Answer 7

Also, based on the example you've given, it looks like you want to get the first inserted value from Table2. Yet, as you've shown, Table2 is a heap w/o a clustered index and there is no date column to show which order the rows were inserted into the table.

So how would be be expected to know which order the rows were inserted? Know that there is a difference in order data is inserted into a clustered index, how it is stored in the data file, and how that data is retrieved. To do what you've shown in your example, you need to give yourself a way to know the insert order of the rows. I would add a datetime column to Table2 with a default value of GETDATE() so that you know the datetime of the insert. This would make the solution to this one a slam dunk.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

SQLRNNR SSC Guru Points: 281344 More actions · Answer 8

Let's throw out there another question.

In table 2 v_id is NVarChar datatype, yet the data shown is INT. Is there a good reason for this? And, as has already been stated, there needs to be some sort of method for determining the insert order into table 2. It almost seems like v_id might have been initially designed to be an Int Identity field. Just a thought.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Grant Fritchey SSC Guru Points: 399278 More actions · Answer 9

The thing you're looking for is the TOP operator (check it out in BOL) but it's going to return nonsense values if you don't supply additional information, basically something to order by, a date, another value that provides some mechanism of differentiating between the multiple entries in the second table. Without that, you'll get different data different times you run the query, even with the same criteria & parameters, as the data changes and the storage moves things around with page splits, etc.

"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