Distinct Record from joined table

You will use a group by.  Here is a simple sample

SELECT     locationid, MIN(eventname) AS EventName, MIN(location_name) as LocationName
FROM         dbo.event
WHERE     (startdate >= '1/1/2012') AND (enddate <= '2/1/2012')
GROUP BY locationid

This will display the location ID, Location Name and Event Name and list the locationid only once.  Notice what is the where clause does not have to show up in the results.

Depending on what info you need from Diary, here the 'ad random' first entry

select d.*, x.* 
from 
(select row_number() over (order by v.distance) rn, v.* 
  from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access
        , internet, restaurants, shops, beach, golf, skiing, tennis, hiking
        , for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR
        , rental_GBP, Problem, Property_Name, Town, Region, Property_Type
        , Property_Description, short_description, bedrooms, sleeps, photo 
        , photo_two, photo_three, thumb, lat, lng
        , (3960 
           * acos( cos( radians(43.64701) ) * cos( radians( lat ) ) 
                       * cos( radians( lng ) - radians(7.00241) ) 
                  + sin( radians(43.64701) ) * sin( radians( lat ) ) 
                 ) 
          ) AS distance 
        FROM Villas
        WHERE for_rent = 1 
        AND Problem = 0 
        AND price_low IS NOT NULL
    ) AS v 
  WHERE v.distance <= 10 
  ) AS x 
JOIN (select Diary.PropID, row_number() over (order by v.PropID) rn
        from Diary
        where Diary.StartDate not between '7-Jan-2012' and '28-Jul-2012'
        group by Diary.PropID
        ) as d
  on x.XID = d.PropID  and d.rn = 1

Select allOpen in new window

When intrested in cumulative info from Diary that would be something like this  

(select Diary.PropID -- min/max/count.....
from Diary
where Diary.StartDate not between '7-Jan-2012' and '28-Jul-2012'
group by Diary.PropID)

Select allOpen in new window

Off topic: I don't see reason why this should be a dynamic sql.

Thanks Jogos - the info in the query is there as an example - the variables are passed to the server when the user selects a date range, location etc. - that's why it's dynamic.

What I'm trying to achieve is for a user to select properties within x distance from a place of interest, then check the availability  -that's another part I have to add in once I get this running!  (I have worked that part out earlier today).

I will give your code a try and let you know - thanks!

Tried it and get an error:

select d.*, x.* from (select row_number() over (order by v.distance) rn, v.* from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access, internet, restaurants, shops, beach, golf, skiing, tennis, hiking, for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR, rental_GBP, Problem, Property_Name, Town, Region, Property_Type, Property_Description, short_description, bedrooms, sleeps, photo, photo_two, photo_three, thumb, lat, lng, (3960 * acos( cos( radians(43.64701) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(7.00241) ) + sin( radians(43.64701) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas WHERE for_rent = 1 AND Problem = 0 AND price_low IS NOT NULL) AS v WHERE v.distance <= 10 ) AS x JOIN (select Diary.PropID, row_number() over (order by v.PropID) rn from Diary where Diary.StartDate not between '7-Jul-2012' and '28-Jul-2012' group by Diary.PropID) as d on x.XID = d.PropID and rn=1
 
Microsoft OLE DB Provider for SQL Server

 error '80040e14'

The multi-part identifier "v.PropID" could not be bound.

/villa-results-list.asp, line 80

Also - how do you make your query string so tidy and get it to work?! - I have to use SQL = SQL & "blah blah" which ends up bunched up as above...!

must be
 row_number() over (order by Diary.PropID)

and in that order also other criteria to get the most wanted row first.

<< the variables are passed to the server when the user selects a date range, location etc>>
For other variables you can use parameters, dynamic sql you only need if the query itself can change (other columns, ....).

Thanks, but still get an error - this query is sooo complicated for me, so I don't want t mess it up... I now get:

select d.*, x.* from (select row_number() over (order by v.distance) rn, v.* from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access, internet, restaurants, shops, beach, golf, skiing, tennis, hiking, for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR, rental_GBP, Problem, Property_Name, Town, Region, Property_Type, Property_Description, short_description, bedrooms, sleeps, photo, photo_two, photo_three, thumb, lat, lng, (3960 * acos( cos( radians(43.64701) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(7.00241) ) + sin( radians(43.64701) ) * sin( radians( lat ) ) ) ) AS distance FROM Villas WHERE for_rent = 1 AND Problem = 0 AND price_low IS NOT NULL) AS v WHERE v.distance <= 10 ) AS x JOIN (select Diary.PropID, row_number() over (order by Diary.PropID) rn from Diary where Diary.StartDate not between '7-Jul-2012' and '28-Jul-2012' group by Diary.PropID) as d on x.XID = d.PropID and rn=1
 
Microsoft OLE DB Provider for SQL Server

 error '80040e14'

Ambiguous column name 'rn'.

/villa-results-list.asp, line 81

Jogos, a couple of days back, you gave me the code at the bottom ofthis message (this is all relating to the same piece of code), which worked fine, however, it brings back the same property, the number of times it has had a booking made - you can see i here: http://gorgeous-villas.com/search-results.asp?lng=7.00241&lat=43.64701&location=Valbonne (the results are hard coded using the query string you supplied)

I need to display each available property only once, hence my questions.

Furthermore, I need to paginate the results, but when I add in ' where rn between 0 and 10' to paginate the results, I get an error:

error '80040e14'

Incorrect syntax near the keyword 'where'.

I need to be able to query the dates, as is shown above, however I do still keep getting these error messages.

I don't know if this is beyond what EE can offer, but would be happy to ask you to sort this for me for an additional fee if need be...!

Please advise - thans in advance

Original Code:
select Diary.*, x.*
from
(select row_number() over (order by v.distance) rn, v.*
  from (SELECT ID as XID, PayMonthly, ccaccepted, pool, sattv, disabled_access
        , internet, restaurants, shops, beach, golf, skiing, tennis, hiking
        , for_rent, for_sale, price_low, rental_currency, rental_USD, rental_EUR
        , rental_GBP, Problem, Property_Name, Town, Region, Property_Type
        , Property_Description, short_description, bedrooms, sleeps, photo
        , photo_two, photo_three, thumb, lat, lng
        , (3960
           * acos( cos( radians(43.64701) ) * cos( radians( lat ) )
                       * cos( radians( lng ) - radians(7.00241) )
                  + sin( radians(43.64701) ) * sin( radians( lat ) )
                 )
          ) AS distance
        FROM Villas
        WHERE for_rent = 1
        AND Problem = 0
        AND price_low IS NOT NULL
    ) AS v
  WHERE v.distance <= 10
  ) AS x
JOIN Diary on x.XID = Diary.PropID
where Diary.StartDate not between '7-Jan-2012' and '28-Jul-2012'

<<Ambiguous column name 'rn'. >>
Take it as a habit when you use different tables always specify the tablename or tablename-alias  with every column

So

and d.rn=1

Select allOpen in new window

<<and I need to return only one record for each property when the Diary is queried - at the moment, I get a record for each booking in the diary, so if a property has had 5 bookings, it comes up 5 times...>>
But what do you need from the bookings-table (Diary) in this query? As I posted higher if you have 5 bookings 5 records get joined so yes it returns 5. If you only want one record returned you can
- take a more or less random record from Diary (but order by in row_number can help to force the most meaningfull -
- only have a cumulative result as a count(*) + group by for having for example the value 5 returned to say for that there are 5 bookings

Pagination is not in the sql but in the code.

Thanks again Jogos.

I need a list of properties which do not cross over with a booking date that a client has entered in the site, and that are within a certain distance of the place they want to rent, with the closest being at the top of the list, so need to order by distance.

If I get 5 records returned for each property (for example), because of the booking table having 5 entries for a property, then my pagination will go out the window, an the results look messy.  I tried the and rn=1 (tried d.rn=1 which didn't work, diary.rn=1 etc. - none work).

I have coded the pagination, using and rn between 0 and 10 when there are more than one records returned.

I have tried count(*) but this has failed every time...!

It's starting to drive me crazy - I think I might have to split the query and work on this a different way, unless you can help me sort this out...!

<< with the closest being at the top of the list, so need to order by distance.>>
at end

ORDER BY x.distance

Select allOpen in new window

<< but this has failed>>
Not something we can work with.

<<which do not cross over with a booking date>>
Or I don't get your data-model or it looks you don't need to join with a booking but look if no booking exists

select x.* 
from 
(.... ) AS x
where not exists (select 1 
 FROM Diary
 WHERE  Diary.PropID  = x.XID
 AND  Diary.StartDate not between '7-Jan-2012' and '28-Jul-2012'
 )
ORDER BY x.distance

Select allOpen in new window

And  with a parameterised query

 AND  Diary.StartDate not between @DateFrom and @DateTo

Select allOpen in new window

Thanks so much Jogos - it runs, but doesn't exclude the property which does have a booking during those dates - seems to be missing it for some reason.

In my sample data, I have two bookings for property 3073, both between 7th July and 28th July, but that property still comes up in the list: http://gorgeous-villas.com/search-results.asp?lng=7.00241&lat=43.64701&location=Valbonne

Any more suggestions...?

(I REALLY APPRECIATE ALL THE HELP!!!)

I'm sure your code would have worked if I had given more info - sorry, but thank you for the great effort you put into helping me.  It simply became too complicated for me - I am a bit of a novice here!  Anyhow, I ended up hiring someone to get it going as I'd already spent 2 weeks going round in circles!

Posting sampling data can help. See you learn from work of hired man.

Thanks Jogos - I hope you will still help me in the future ;-)

No prob.