Avatar of Nico2011
Nico2011
Flag for United Kingdom of Great Britain and Northern Ireland asked on

Distinct Record from joined table

Hello Experts,

Please can let me know how I would retrieve a unique record fro a joined table please?

My query is:

SQL = "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 LEFT JOIN Diary on Diary.PropID = x.XID where Diary.StartDate not between '01-Jul-2012' and '28-Jul-2012' AND Diary.StartDate"

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...

Thanks in advance.
Microsoft SQL Server 2008ASP

Avatar of undefined
Last Comment
jogos

8/22/2022 - Mon
Scott Fell

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.
jogos

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

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

Open in new window


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

ASKER
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!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Nico2011

ASKER
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...!
jogos

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, ....).
Nico2011

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Nico2011

ASKER
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'
jogos

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

Open 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.
Nico2011

ASKER
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...!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
jogos

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

Open 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

Open in new window



And  with a parameterised query
 AND  Diary.StartDate not between @DateFrom and @DateTo

Open in new window

Nico2011

ASKER
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!!!)
ASKER CERTIFIED SOLUTION
jogos

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Nico2011

ASKER
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!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jogos

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

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

No prob.
Your help has saved me hundreds of hours of internet surfing.
fblack61