Tricky JOIN?? - SQL Server Forums

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)

Tricky JOIN??

Author

Topic

lewiska1
Starting Member

12 Posts

Posted - 2004-07-25 : 13:50:48

Hi guys,

I'm doing a small book review site. I have one table for books and another for reviews.

I want to generate a list of the 10 most recently added books, along with their most recent review. That list must still show the book even if there are no reviews for it yet(I think I got that part to work by itself using a LEFT JOIN).

I have tried seemingly everything but just can't seem to come up with a working query for this.

The basic table structures are :
BOOKS - ID,Name,Author,Date_Added
REVIEWS - ID,Book_ID,Review,Review_Date,Reviewed_By

Thanks for your help guys.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 14:02:53

I have no idea if this will work since you didn't post a create table statement and some sample data. :)




SELECT TOP 10
	b.ID,
	b.Name,
	b.Author,
	b.Date_Added,
	r.Review,
	r.Review_Date,
	r.Reviewed_By
FROM
	BOOKS b
	LEFT OUTER JOIN REVIEWS r ON b.ID = r.Book_ID
	INNER JOIN (
		SELECT
			Book_ID,
			MAX(ID) AS ID
		FROM
			REVIEWS
		GROUP BY Book_ID) r2 ON r.ID = r2.ID

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

lewiska1
Starting Member

12 Posts

Posted - 2004-07-25 : 14:48:50

Hi Derrick,

Thanks for the quick response. I get the following error ...

quote:
Syntax error (missing operator) in query expression 'b.ID = r.Book_ID INNER JOIN ( SELECT Book_ID, MAX(ID) AS ID FROM REVIEWS GROUP BY Book_ID) r2 ON r.ID = r2.ID'.

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 14:51:44

lewiska1, chuck:


ORDER BY b.Date_Added DESC

on the bottom of Derrick's example

Kristen HelpfulYoungDBA

EDIT: Posted before I saw your reply, this won;t fix that problem, but you'll need it anyway

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 14:53:27

DOH

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-25 : 14:57:25

Looks OK to me, but maybe I'm going blind. Are you using SQLServer or something else like Access?

Kristen

lewiska1
Starting Member

12 Posts

Posted - 2004-07-25 : 15:22:12

Ooops, sorry, I am using Access to develop (it's a small site) and would've moved to SQL Server later on. I'd thought the queries would've been pretty much the same...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-25 : 15:55:43

Unfortunately, they are not when you are doing nested queries and derived tables.

I'm not an expert at Access (I don't even consider myself good at it). I believe you are going to need to do this with three queries though.

qryMaxReviews:

SELECT Book_ID, MAX(ID) AS Max_ID
FROM REVIEWS
GROUP BY Book_ID

qryEligibleReviews:

SELECT r.Book_ID, r.Review, r.Review_Date, r.Reviewed_By
FROM REVIEWS r INNER JOIN qryMaxReviews mr ON r.ID = mr.Max_ID

qryTopBookReviews:


SELECT TOP 10
 b.ID,
 b.Name,
 b.Author,
 b.Date_Added,
 r.Review,
 r.Review_Date,
 r.Reviewed_By
FROM
 BOOKS b
 INNER JOIN qryEligibleReviews r ON b.ID = r.Book_ID

I'm sure someone on here can show you a better way.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

lewiska1
Starting Member

12 Posts

Posted - 2004-07-25 : 23:19:52

I'm using ASP and ADO and I'm not even sure how I could use the results of one query in another.

Hmmmmm ... unless I someone else suggests another way that works with ASP, ADO and Access, it looks like SQL Server time already ...

Thanks guys.

Kristen
Test

22859 Posts

Posted - 2004-07-26 : 01:31:20

You know it makes sense

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-26 : 08:41:49

open up access, and the create the two queries as described. it's pretty easy. go to the queries tab and click "New...".

that's it. you can open a query just like any other table through ADO.

- Jeff

lewiska1
Starting Member

12 Posts

Posted - 2004-07-26 : 16:54:35

Great idea for doing it with Access. I never even thought of that. It'll solve my problem in the short term.

Thanks guys. I hope some of your greatness rubs off on me eventually!!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-26 : 23:44:26

quote:
Originally posted by jsmith8858

open up access, and the create the two queries as described. it's pretty easy. go to the queries tab and click "New...".

that's it. you can open a query just like any other table through ADO.

- Jeff

I'm beginning to feel as if I don't speak English. Is this the case? Can you help me out here man? I'm feeling abused.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

lewiska1
Starting Member

12 Posts

Posted - 2004-07-27 : 18:39:55

Thanks guys,

Just in case anyone is interested, the final query didn't return the expected results. I went back over each query to check it's results and the other two were ok. So I changed the INNER to LEFT in the last one and voila, perfection!!

Again, many, many, many thanks.

PS. derricklegget, thanks a million!!

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-27 : 22:42:45

quote:
Originally posted by derrickleggett

I'm beginning to feel as if I don't speak English. Is this the case? Can you help me out here man? I'm feeling abused.

Wouldn't you rather have your friends abuse you instead of having to abuse yourself ?

-PatP

Itinerant Curmudgeon

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-28 : 08:54:21

I'll have to think about that one Pat. Perhaps I should take off work and ponder on it for awhile.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Pat Phelan
Posting Yak Master

187 Posts

Posted - 2004-07-28 : 09:02:24

quote:
Originally posted by derrickleggett

I'll have to think about that one Pat. Perhaps I should take off work and ponder on it for awhile.

Very good plan! I will to, and we can meet in Hawaii to pontificate upon our ponderings!

-PatP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources