Fetching sungle record from join

Hello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul JhaHello folks,
I have a table, say T1 and this has a child tabel named T2. The common column between the tables are say COL. Now the scenario is there are multiples of records in the T2 for each record in table T1.

Now when i make a join of both the tables, say INNER JOIN, it returns the number of records based on the child table. i.e. say for a record in T1 there are 3 records in T2. Then through the INNER JOIN i will be getting the 3 records. But need only one record from the join. Have tried with "SET ROWCOUNT 1". But as you all know that this will not work. Kind suggest me the way friends......:eek: :eek: :eek:

Thanks,
Rahul Jha

Are they 3 identical records, or is there something different about them ? If they are identical you can cheese it with a distinct or a group by.|||which one do you want?|||I've never heard of a sungle record|||Rudy asks the correct question here - which of the 3 corresponding records do you want to return? And the answer "it doesn't matter/any of them" doesn't cut it ;)|||And the answer "it doesn't matter/any of them" doesn't cut it ;)Why? He could simply using MAX() or MIN() to get only one record|||MAX or MIN will of course return only one value out of the joined row

what about "the row with the max value"|||Just checking in, pulling up a chair, putting my feet up on the ottoman, leaning back, opening a beer, putting my 3-D glasses on, and waiting for the show...|||BTW, Brett, a "sungle row" is simply a Single row from amongst a Jungle of rows.|||Or he's from New Zealand|||I just might take a stab at this one.

It sounds like rows from t2 are different in some way. If you had data in t2 having to do with say a person and all of the phone numbers they could possibly have, you would get a different row for every phone number.

This is of course, if I am understanding the question correctly.

Feasibility of Filtering in this Scenario...

Background:
I'm a young developer working on a project that involves merge replication between SQL Server 2005 Mobile Edition and SQL Server 2005 and I've been having a hard time wrapping my head around exactly how to implement the best filters for the subscription(s) and publication(s).

Users of our application are Auction goers who collect data about AuctionItems in a mobile db and sync that information with a central db once he or she is finished. The central db is, obviously, pre-populated with most of the information about any/all AuctionItems. Central db information is available to view/change via web access.

Multiple users can change the same Auction data so there will be overlapping partitions.

What I would like to do is:
Present the user with a list of all available Auctions for the next 2 weeks (select * from Auctions where blah blah blah). This is a simple publication to create. Now, I'd like to be able allow the user to select AuctionID 4, AuctionID 3, AuctionID 12 and then, via a seperate AuctionListing publication only download AuctionItems that apply to those IDs.

Obviously, the publication has to be created and include the AuctionItem table and all necessary related tables/columns. But how do I create a parameterized filter based on that AuctionID?

Can a subscription be "dynamically created" once those AuctionIDs are known? Obviously, I don't want to bog my mobile devices down with tens of thousands of auction listings to auctions the user is not planning to attend.

Basically, my question is: am I wasting my time or is there some clever manipulation of HOST_NAME(), SUSER_NAME(), both, or some other method that I've missed that can get only those Auctions where the the ID matches one selected by the user at runtime?

You can do it with either one publication or 2 publications.

With one publication, I can think of dynamically populating a table with the IDs from what the user chooses. The filter will be based on IDs present in this table. However note that populating the table and synchronization need to be in 2 different transactions because if they are in the same one, enumerations will be looking at not correct data.

Let me know if you have trouble setting the filter.

|||Could you elaborate a little more on some of the parameters/steps involved involved with the filtering process?

How would we do it, exactly, in one publication? Do I need to use a

mix of RDA and merge replication (or is that even possible)?

In my head I think I know how it needs to work but, as a SQL Server beginner (just started using it this year), I don't know if I can describe the problem and solution eloquently with the available tools.|||Mahesh:

I've decided to go with one publication. How can I separate the population of the database dynamically from the sync call that I will need to make to pull the results down? How do split this action into separate transactions.|||One more thing to consider: I can see the feasibility of using two publications to accomplish this tasks and in some ways it might be easier. Nevertheless, consider this scenario.

We use a table called 'AuctionDownload' that is populated with the AuctionIDs and some string value that we can filter on using HOST_NAME(). This table will be included in the publication along with the main 'Auction' table.

User A subscribes, calls sync, gets a list of auctions and chooses IDs 1 and 2 that he wants listings for. He populates the rows in 'AuctionDownload' on his mobile device and syncs. That's fine.

However, imagine User B is also using the system at the same time as User A. The first time he calls sync to get the list of available auctions, his EMPTY AuctionDownload table will need to be merged with the central AuctionDownload table. Thus User A's values are erased before he or she could call sync on a different publication to get the actual listings!

Is there a work around or am I imagining this syncing scenario incorrectly.