Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old November 15th, 2004, 01:43 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default SELECT TOP 1 with INNER JOIN

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 :)


  #2 (permalink)  
Old November 15th, 2004, 03:22 AM
Friend of Wrox
Points: 2,473, Level: 20
Points: 2,473, Level: 20 Points: 2,473, Level: 20 Points: 2,473, Level: 20
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: India
Posts: 642
Thanks: 0
Thanked 43 Times in 42 Posts
Default

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
  #3 (permalink)  
Old November 15th, 2004, 03:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #4 (permalink)  
Old November 15th, 2004, 03:35 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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


  #5 (permalink)  
Old November 15th, 2004, 03:54 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

_________________________
- Vijay G
Strive for Perfection
  #6 (permalink)  
Old November 15th, 2004, 11:29 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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

  #7 (permalink)  
Old November 16th, 2004, 02:28 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

What is the error it shows?

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

_________________________
- Vijay G
Strive for Perfection
  #8 (permalink)  
Old November 16th, 2004, 03:39 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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"?>

  #9 (permalink)  
Old November 16th, 2004, 03:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #10 (permalink)  
Old November 16th, 2004, 03:57 AM
Authorized User
 
Join Date: Sep 2004
Location: Sydney, NSW, Australia.
Posts: 67
Thanks: 1
Thanked 0 Times in 0 Posts
Default

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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
SELECT TOP n rgerald SQL Server 2000 3 May 12th, 2006 04:03 PM
SELECT TOP n NOT SELECTING TOP n! ibi SQL Language 8 March 30th, 2005 08:08 PM
Join Query Distinct and Top 1 ahanson SQL Language 4 November 30th, 2004 01:29 PM
SELECT TOP FROM HAVING khatfield29 SQL Language 1 August 23rd, 2004 02:41 PM
Need help with TOP 5 and self join funkedup SQL Language 2 May 17th, 2004 11:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.