SELECT TOP 1 with INNER JOIN

spinout · November 15th, 2004, 01:43 AM

Hi There
I am using a SQL statement that beings with SELECT TOP 1 and also has an INNER JOIN.

Basically, I only want to select the first row from the first table but this table has many records associated with it in the second table and I only get the first row returned for the second table as well.

I want to be able to get all of the associated rows from the second table that match the top row I select from the first. How would I achieve this?

Thanks for your help :)

om_prakash · November 15th, 2004, 03:22 AM

Hi,

If you have top 1 in the first query then you will get only one record with or without inner join. You can use left join and take the second table at the beginning. This will give u all records from second table and have a join with first table.

Om Prakash

happygv · November 15th, 2004, 03:28 AM

Why dont you try this.

Code:

Select top 1 a.* 
from table1 a, table2 b
where a.idcolumn=b.idcolumn
order by the relavant_column

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

spinout · November 15th, 2004, 03:35 AM

Hi There

So do you mean I should have a query that looks something like this:

SELECT TOP 1 * FROM tableOne
LEFT JOIN tableTwo ON tableOne.ID = tableTwo.ID

happygv · November 15th, 2004, 03:54 AM

What does that return? Does the result match with your requirement?

_________________________
- Vijay G
Strive for Perfection

spinout · November 15th, 2004, 11:29 AM

No it doesn't, it gives me an error.

happygv · November 16th, 2004, 02:28 AM

What is the error it shows?

Did you try the query that I suggested in one my post?

_________________________
- Vijay G
Strive for Perfection

spinout · November 16th, 2004, 03:39 AM

Quote:

quote:Originally posted by happygv
What is the error it shows?

Did you try the query that I suggested in one my post?

Yes, I also tried your query and both gave me this error message:

<?MSSQLError HResult="0x80004005" Source="Microsoft XML Extensions to SQL Server" Description="Streaming not supported over multiple column result"?>

happygv · November 16th, 2004, 03:48 AM

Looks like you are trying to run that from frontend app. Did you ever try to test that from SQL query analyser? Was that successfully returing any rows when tried from SQL query analyser?

_________________________
- Vijay G
Strive for Perfection

spinout · November 16th, 2004, 03:57 AM

Quote:

quote:Originally posted by happygv
Looks like you are trying to run that from frontend app. Did you ever try to test that from SQL query analyser? Was that successfully returing any rows when tried from SQL query analyser?

I'm trying to run it though SQL's XML support for IIS tool that has a virtual directory setup that it supposed to query the databaase and return the results in XML format.

I did try running the query in Query Analyser and I didn't get an error message, but I did still only get the top row of the second table rather than all the related rows with the same ID in the second table as the top row of the primary table.