SELECT TOP 1 with INNER JOIN

khng · November 16th, 2004, 04:08 AM

Quote:

quote:Originally posted by spinout
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 :)

How about this:
SELECT *
FROM child_table
WHERE child_table.key=(SELECT TOP 1 primary_key
FROM parent_table);

it seem that subquery is required for your situation

spinout · November 16th, 2004, 04:14 AM

Yes that worked. However it only returned all the fields from the child table that matched the top row ID column in the primary table. I need to be able to return multiple columns in the top row of the primary table rather than just the ID field.

I tried doing this by modifying the query but I got an error message saying "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

khng · November 16th, 2004, 04:28 AM

Quote:

quote:Originally posted by spinout
Yes that worked. However it only returned all the fields from the child table that matched the top row ID column in the primary table. I need to be able to return multiple columns in the top row of the primary table rather than just the ID field.

I tried doing this by modifying the query but I got an error message saying "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS."

How about this
SELECT *
FROM child_table, parent_table
WHERE foreign_key=primary_key AND primary_key=(SELECT top 1 primary_key FROM parent_table);

A modified version of my suggestion.....
but the performance isn't as fast as before

spinout · November 16th, 2004, 04:40 AM

That worked!! Thanks :)

happygv · November 22nd, 2004, 08:36 PM

Hi spinout,

You could have just added this to the query I posted earlier.

Code:

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

OR

Code:

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

Cheers!

_________________________
- Vijay G
Strive for Perfection