January 27, 2012 at 3:16 pm
I have the following SQL statement:
SELECT m.[property_id], m.[Address], m.[City], m.[Zip], m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms, m.[V_O], m.[Stars], m.Rehab, m.Open_bid, m.Notes, m.date_time_changed, i.imagepaththumb FROM [Master] m LEFT JOIN Image i On property_id = property_id_fk where Active_Month='Y' ORDER BY m.[V_O] DESC, m.date_time_changed desc, m.[Address] ASC
The problem I'm having writing this sql statement is when I'm populating my gridview in asp.net if an imagepaththumb has more than one picture for that property I'm getting duplicate rows because there's different pictures. How would I use this following sql statement and just get the "top" entry for that property so I can put a logo showing that property has a picture? Can someone please help me with this!
Thanks
January 27, 2012 at 3:38 pm
Something like this get you close?
--Note this is untested because there is nothing to test against.
SELECT top 1 m.[property_id], m.[Address], m.[City], m.[Zip], m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms,
m.[V_O], m.[Stars], m.Rehab, m.Open_bid, m.Notes, m.date_time_changed, i.imagepaththumb
FROM [Master] m
LEFT JOIN Image i On property_id = property_id_fk
where Active_Month='Y'
group by m.[property_id], m.[Address], m.[City], m.[Zip], m.County, m.Bedrooms, m.Bathrooms, m.HalfBathrooms,
m.[V_O], m.[Stars], m.Rehab, m.Open_bid, m.Notes, m.date_time_changed, i.imagepaththumb
ORDER BY m.[V_O] DESC, m.date_time_changed desc, m.[Address] ASC, i.MyColumnThatTellsMeItIsFirst
You will have to figure out which column in your image table to sort by.
A bit off topic but you really should try to avoid reserved words as object names (master, Address, etc). Also, in my experience I find it very frustrating over time to work on systems that have property_id in one table and that name changes to property_id_fk in another table. Maybe it is just semantics but it is still the property_id referring to the same thing. I have to deal with things like product_pk in one table becomes product_fk in another table. It is terribly frustrating to write queries against structures like that. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 27, 2012 at 3:58 pm
This only shows one property.
I want to be able to show all the properties in the database and if they have one picture or 20 pictures i want to be able to display one image in the gird showing that there's a picture in the grid, because right now each image that gets inserts in my code creates an imagepaththumb. I want to call just one imagepaththumb per one distinct foreign key. Is there not a way to do this?
January 30, 2012 at 7:01 am
tommy.ray (1/27/2012)
This only shows one property.I want to be able to show all the properties in the database and if they have one picture or 20 pictures i want to be able to display one image in the gird showing that there's a picture in the grid, because right now each image that gets inserts in my code creates an imagepaththumb. I want to call just one imagepaththumb per one distinct foreign key. Is there not a way to do this?
I don't know what you mean that it only shows one property? Did you try the query I posted? It will return 1 row for each property, that is what that group by will do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 30, 2012 at 7:06 am
Use an OUTER APPLY
SELECT m.[property_id]
,m.[Address]
,m.[City]
,m.[Zip]
,m.County
,m.Bedrooms
,m.Bathrooms
,m.HalfBathrooms
,m.[V_O]
,m.[Stars]
,m.Rehab
,m.Open_bid
,m.Notes
,m.date_time_changed
,i.imagepaththumb
FROM [Master] m
OUTER APPLY (
SELECT TOP (1) impagepaththumb
FROM Image
WHERE property_id = property_id_fk
) AS i
where Active_Month='Y'
ORDER BY m.[V_O] DESC
,m.date_time_changed desc
,m.[Address] ASC
You could also use ROW_NUMBER(), but I find that ROW_NUMBER tends to work better when there is only one table and OUTER APPLY tends to work better when you want to limit the records returned in a one-to-many join.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply