selecting only first record of the left join table

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 2000 Forums

Transact-SQL (2000)

selecting only first record of the left join table

Author

Topic

tolisss
Starting Member

17 Posts

Posted - 2005-11-22 : 16:18:23

Hi
here is my problem .
i have 2 tables table1 1-n with table2
i want to make a query that selects all records from table1 and only the first record of table2

to give an example
suppose table1 have a field Table1ID and first record of table Table1ID =1.

That Table1ID has 2 records on table2 (fields on table2 are Table2ID ,table1ID)
Table2ID table1ID
1 1
2 1

my query should have the following fields and records

Table1ID Table2ID
1 1

how can i do that?

thnnks in advance

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-11-22 : 18:26:07

Hi tolisss,

Try this query:

SELECT *
FROM Table1 LEFT OUTER JOIN (SELECT Table1ID, MIN(Table2ID) FROM Table2
GROUP BY Table1ID) Table2
ON Table1.Table1ID = Table2.Table1ID

Hope this helps.

http://www.sql-server-helper.com

tolisss
Starting Member

17 Posts

Posted - 2005-11-22 : 19:10:22

thnk u very much it work well!!!!

may i ask 1 more question? could u say some hints on when and how to use statements like this (SELECT Table1ID, MIN(Table2ID) FROM Table2
GROUP BY Table1ID) b4 the join?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 00:58:06

When you want to select minimum Table2ID based on Table1Id, you can use that

Madhivanan

Failing to plan is Planning to fail

drafner
Starting Member

1 Post

Posted - 2005-12-01 : 17:16:23

A quick problem that I have that seems similar is to pull only the first matching record in a 1-M relationship. Perhaps you can help me with it?

For example,
Employees has EMP_ID(PK),EMP_NAME
Emp_Accts has ACCT_NUM(PK), EMP_ID
An employee can have many bank accounts.
The query should return the EMP_ID, EMP_NAME, ACCT_NUM (But just the first account from the group).

I seem to recall that perhaps the preferred way is to reference the sequence. Any suggestions?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-02 : 01:20:50

It is better to post your question as a new topic to get more replies
Post some sample data and the result you want

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources