Need Help with a Joined table Query!

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 2005 Forums

Transact-SQL (2005)

Need Help with a Joined table Query!

Author

Topic

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-03 : 14:29:56

Hello guys, I have 2 tables. One table has intormation about a listing, and the other table contains the URL's for the images for that listing.. they are joined by an ID number equal to table A.

Problem is, if there are 10 images uploaded, my query returns 10 rows of the same listing but with each thumbnail picture different. I just want it to return the first row... Can anyone help me with this? Here's my query:

SELECT db_UserListings.ListingID, db_UserListings.UserID, db_UserListings.ListingDate, db_UserListings.ListingSubject, db_UserListings.PriceAsked,
db_UserListings.Catagory_ID, db_UserListings.SubCatagory_ID, db_UserListings.State_ID, db_UserListings.City_ID,
db_UserListings.ActiveListing, db_ForSalePics.Thumb
FROM db_UserListings inner JOIN
db_ForSalePics ON db_UserListings.ListingID = db_ForSalePics.ListingID
WHERE (db_UserListings.ListingSubject LIKE '%' + @subject + '%') OR
(db_UserListings.Catagory_ID = @catid) OR
(db_UserListings.SubCatagory_ID = @subcatid) OR
(db_UserListings.State_ID = @stateid) OR
(db_UserListings.City_ID = @cityid)

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-03 : 14:33:43

Change


SELECT


SELECT TOP 1

which would return only the 1st row (and only 1 row) returned by the query. Are you sure you don't want the "most recent" or some other filter?

perhaps the last date for the other unique entries?


SELECT db_UserListings.ListingID, db_UserListings.UserID, max(db_UserListings.ListingDate) as ListingDate, db_UserListings.ListingSubject, db_UserListings.PriceAsked,
db_UserListings.Catagory_ID, db_UserListings.SubCatagory_ID, db_UserListings.State_ID, db_UserListings.City_ID,
db_UserListings.ActiveListing, db_ForSalePics.Thumb
FROM db_UserListings inner JOIN
db_ForSalePics ON db_UserListings.ListingID = db_ForSalePics.ListingID
WHERE (db_UserListings.ListingSubject LIKE '%' + @subject + '%') OR
(db_UserListings.Catagory_ID = @catid) OR
(db_UserListings.SubCatagory_ID = @subcatid) OR
(db_UserListings.State_ID = @stateid) OR
(db_UserListings.City_ID = @cityid)
GROUP BY db_UserListings.ListingID, db_UserListings.UserID, db_UserListings.ListingSubject, db_UserListings.PriceAsked,
db_UserListings.Catagory_ID, db_UserListings.SubCatagory_ID, db_UserListings.State_ID, db_UserListings.City_ID,
db_UserListings.ActiveListing, db_ForSalePics.Thumb

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-03 : 14:40:22

I tried doing a TOP 1, but lets say in the listing table i have two "subjects" with the same word in it. It only returns 1 listing... this is going to be displayed in a gridview in asp .net...

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-03 : 14:43:10

And i tried your other way.. Still doesnt work.. still displaying 10 listings when there is only 1...

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-03 : 14:47:12

Well, do you want "all" matching subjects, or the top row for each listing?

You would have to use a subquery(derived table) and inner join to that to pull the unique id that exists to differentiate the 10 listings with different pics (but other info the same). I would assume there is another identifier to separate these 10? (I am assuming that listing date and other information is static in all 10 rows of your example, and am also hoping that while there are 10 pics, there is a nother field that can uniquely identify each row)

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-03 : 14:53:37

All i want it to return is the all listings that match the subject, and the first row's thumb inside the images table. No, i do not have another unique identifier within the images table.. although since i just developed this table, its still new enough to.

Let me know what you think

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-03 : 16:06:49

Use the row number function. It will allow more flexability, and in my opinion a better practice.


SELECT	  a.ListingID
		, a.UserID
		, a.ListingDate
		, a.ListingSubject
		, a.PriceAsked
		, a.Catagory_ID
		, a.SubCatagory_ID
		, a.State_ID
		, a.City_ID
		, a.ActiveListing
		, b.Thumb
FROM 
	db_UserListings a
inner JOIN
	(
		Select Row_Number() over (Partition by aa.ListingID order by aa.thumb) as RowID,aa.*
		from db_ForSalePics aa
	 ) b
ON a.ListingID = b.ListingID
WHERE 
	b.RowID = 1
and
(		a.ListingSubject LIKE '%' + @subject + '%'
OR		a.Catagory_ID = @catid 
OR		a.SubCatagory_ID = @subcatid
OR		a.State_ID = @stateid
OR		a.City_ID = @cityid
)

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-03 : 16:35:17

Also from looking at your query I am assuming you want to use 'and' not 'or' with your where clause. The way you have it written, someone can only select 1 filter, if you use what is illustrated below you can have multiple.


WHERE
                b.RowID = 1
AND		a.ListingSubject LIKE '%' + coalesce(@Subject,a.ListingSubject) + '%'
AND		a.Catagory_ID = coalesce(@catid,a.Catagory_ID)
AND		a.SubCatagory_ID = coalesce(@subcatid,a.SubCatagory_ID)
AND		a.State_ID = Coalesce(@stateid,a.State_ID)
AND		a.City_ID = Coalesce(@cityid,a.City_ID)

Mnemonic
Yak Posting Veteran

58 Posts

Posted - 2007-11-03 : 16:40:12

Vinne -

All i can say is WOW! Thanks so much, everything is working perfectly now!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources