Best way to handle the previous next (one record)

well, yeah, that would explain why i asked to see the “real” query

you have only a couple of columns from the properties table in your SELECT clause, which really makes one wonder why you need to join to all those other tables

you understand?

okay, before i attempt to write the sql, let me ask you a question

suppose you are navigating through the properties, and you happen to be sitting on a particular property, and let’s say it has 42 views, and you want to see the “next” property, which, because your query has ORDER BY views DESC, would be the next lower number…

… and suppose there are two properties which have 40 views

which one is “next” ?

please don’t say “either” or “just pick one” – because sql can’t do that

you have to have some value in some column which can be used to break the tie

do you understand the problem here?

EDIT: oh, wait… you said there is another column in the ORDER BY?

mind explaining it?

Indeed Rudy, the number of columns to be queried vary in listing page and the details page because I need to display more fields in details page than in listing. The query I have shown is for the listing page.

And why I have joined all the tables is that I just want to make sure and query all those properties which have rows in all the related tables because some of the tables might not have property information. If this can be done in some other ways from the SQL itself, it would be great to know.

I think I understood what you mean. That’s why I have another field in order by clause that is unique id (property id). So the final query seems like this:


SELECT 
    p.id,p.title,p.views
FROM 
    jos_prpt_properties AS p 
    INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id 
    INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id 
    INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id 
    INNER JOIN jos_prpt_features AS f ON p.id=f.property_id 
    INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id 
    INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id 
    INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC, p.id ASC
LIMIT 10,1;

Hope this clear now.

Thank you rudy for taking time to care the problem:).

SELECT p.id    , p.title    , p.views
     , next.id , next.title , next.views
     , prev.id , prev.title , prev.views           
FROM 
    jos_prpt_properties AS p 

LEFT OUTER
  JOIN jos_prpt_properties AS [COLOR="Red"]next[/COLOR]
    ON [COLOR="red"]next.id [/COLOR]= ( SELECT [COLOR="red"]MIN(id)[/COLOR]
                     FROM ( SELECT id
                              FROM jos_prpt_properties
                            INNER
                              JOIN jos_prpt_listingtype 
                                ON jos_prpt_listingtype.id 
                                 = jos_prpt_properties.listing_type 
                            INNER 
                              JOIN jos_prpt_features 
                                ON jos_prpt_features.property_id 
                                 = jos_prpt_properties.id
                             WHERE jos_prpt_properties.published = 1
                               AND jos_prpt_listingtype IN (1,3) 
                               AND jos_prpt_features.bedrooms >= 4 
                               AND views = 
                                   ( SELECT [COLOR="red"]MAX(views)[/COLOR]
                                       FROM jos_prpt_properties
                                     INNER
                                       JOIN jos_prpt_listingtype 
                                         ON jos_prpt_listingtype.id 
                                          = jos_prpt_properties.listing_type 
                                     INNER 
                                       JOIN jos_prpt_features 
                                         ON jos_prpt_features.property_id 
                                          = jos_prpt_properties.id
                                      WHERE jos_prpt_properties.published = 1
                                        AND jos_prpt_listingtype IN (1,3) 
                                        AND jos_prpt_features.bedrooms >= 4 
                                        AND [COLOR="red"]views < p.views [/COLOR])
                          ) ) 

LEFT OUTER
  JOIN jos_prpt_properties AS [COLOR="Blue"]prev[/COLOR]
    ON [COLOR="blue"]prev.id [/COLOR]= ( SELECT [COLOR="blue"]MAX(id)[/COLOR]
                     FROM ( SELECT id
                              FROM jos_prpt_properties
                            INNER
                              JOIN jos_prpt_listingtype 
                                ON jos_prpt_listingtype.id 
                                 = jos_prpt_properties.listing_type 
                            INNER 
                              JOIN jos_prpt_features 
                                ON jos_prpt_features.property_id 
                                 = jos_prpt_properties.id
                             WHERE jos_prpt_properties.published = 1
                               AND jos_prpt_listingtype IN (1,3) 
                               AND jos_prpt_features.bedrooms >= 4 
                               AND [COLOR="blue"]views [/COLOR]= 
                                   ( SELECT [COLOR="blue"]MIN(views)[/COLOR]
                                       FROM jos_prpt_properties
                                     INNER
                                       JOIN jos_prpt_listingtype 
                                         ON jos_prpt_listingtype.id 
                                          = jos_prpt_properties.listing_type 
                                     INNER 
                                       JOIN jos_prpt_features 
                                         ON jos_prpt_features.property_id 
                                          = jos_prpt_properties.id
                                      WHERE jos_prpt_properties.published = 1
                                        AND jos_prpt_listingtype IN (1,3) 
                                        AND jos_prpt_features.bedrooms >= 4 
                                        AND [COLOR="blue"]views > p.views [/COLOR])
                          ) ) 

    INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id 
    INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id 
    INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id 
    INNER JOIN jos_prpt_features AS f ON p.id=f.property_id 
    INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id 
    INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id 
    INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC;

Thank you so much Rudy, I will give it a try but I am confused I will be able to understand this query quickly and implement because when I try to run the following query:


SELECT p.id    , p.title    , p.views     
FROM 
    jos_prpt_properties AS p 
LEFT OUTER
  JOIN jos_prpt_properties AS NEXT
    ON next.id = ( SELECT MIN(id)
                     FROM ( SELECT id
                              FROM jos_prpt_properties
                            INNER
                              JOIN jos_prpt_listingtype 
                                ON jos_prpt_listingtype.id 
                                 = jos_prpt_properties.listing_type 
                            INNER 
                              JOIN jos_prpt_features 
                                ON jos_prpt_features.property_id 
                                 = jos_prpt_properties.id
                             WHERE jos_prpt_properties.published = 1
                               AND jos_prpt_listingtype IN (1,3) 
                               AND jos_prpt_features.bedrooms >= 4 
                               AND views = 
                                   ( SELECT MAX(views)
                                       FROM jos_prpt_properties
                                     INNER
                                       JOIN jos_prpt_listingtype 
                                         ON jos_prpt_listingtype.id 
                                          = jos_prpt_properties.listing_type 
                                     INNER 
                                       JOIN jos_prpt_features 
                                         ON jos_prpt_features.property_id 
                                          = jos_prpt_properties.id
                                      WHERE jos_prpt_properties.published = 1
                                        AND jos_prpt_listingtype IN (1,3) 
                                        AND jos_prpt_features.bedrooms >= 4 
                                        AND views < p.views )
                          ) ) 
LEFT OUTER
  JOIN jos_prpt_properties AS PREV
    ON prev.id = ( SELECT MAX(id)
                     FROM ( SELECT id
                              FROM jos_prpt_properties
                            INNER
                              JOIN jos_prpt_listingtype 
                                ON jos_prpt_listingtype.id 
                                 = jos_prpt_properties.listing_type 
                            INNER 
                              JOIN jos_prpt_features 
                                ON jos_prpt_features.property_id 
                                 = jos_prpt_properties.id
                             WHERE jos_prpt_properties.published = 1
                               AND jos_prpt_listingtype IN (1,3) 
                               AND jos_prpt_features.bedrooms >= 4 
                               AND views = 
                                   ( SELECT MIN(views)
                                       FROM jos_prpt_properties
                                     INNER
                                       JOIN jos_prpt_listingtype 
                                         ON jos_prpt_listingtype.id 
                                          = jos_prpt_properties.listing_type 
                                     INNER 
                                       JOIN jos_prpt_features 
                                         ON jos_prpt_features.property_id 
                                          = jos_prpt_properties.id
                                      WHERE jos_prpt_properties.published = 1
                                        AND jos_prpt_listingtype IN (1,3) 
                                        AND jos_prpt_features.bedrooms >= 4 
                                        AND views > p.views )
                          ) ) 
    INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id 
    INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id 
    INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id 
    INNER JOIN jos_prpt_features AS f ON p.id=f.property_id 
    INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id 
    INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id 
    INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC;

I got the following error:


Error Code : 1248
Every derived table must have its own alias

So I think I need to study the query deeply and find out where to implement it and what the error is being caused.

“Every derived table must have its own alias” was my fault, sorry

try this –

SELECT p.id    , p.title    , p.views     
FROM 
    jos_prpt_properties AS p 
LEFT OUTER
  JOIN jos_prpt_properties AS NEXT
    ON next.id = ( SELECT MIN(id)
                     FROM ( SELECT id
                              FROM jos_prpt_properties
                            INNER
                              JOIN jos_prpt_listingtype 
                                ON jos_prpt_listingtype.id 
                                 = jos_prpt_properties.listing_type 
                            INNER 
                              JOIN jos_prpt_features 
                                ON jos_prpt_features.property_id 
                                 = jos_prpt_properties.id
                             WHERE jos_prpt_properties.published = 1
                               AND jos_prpt_listingtype IN (1,3) 
                               AND jos_prpt_features.bedrooms >= 4 
                               AND views = 
                                   ( SELECT MAX(views)
                                       FROM jos_prpt_properties
                                     INNER
                                       JOIN jos_prpt_listingtype 
                                         ON jos_prpt_listingtype.id 
                                          = jos_prpt_properties.listing_type 
                                     INNER 
                                       JOIN jos_prpt_features 
                                         ON jos_prpt_features.property_id 
                                          = jos_prpt_properties.id
                                      WHERE jos_prpt_properties.published = 1
                                        AND jos_prpt_listingtype IN (1,3) 
                                        AND jos_prpt_features.bedrooms >= 4 
                                        AND views < p.views )
                          ) [COLOR="red"]AS humpty [/COLOR]) 
LEFT OUTER
  JOIN jos_prpt_properties AS PREV
    ON prev.id = ( SELECT MAX(id)
                     FROM ( SELECT id
                              FROM jos_prpt_properties
                            INNER
                              JOIN jos_prpt_listingtype 
                                ON jos_prpt_listingtype.id 
                                 = jos_prpt_properties.listing_type 
                            INNER 
                              JOIN jos_prpt_features 
                                ON jos_prpt_features.property_id 
                                 = jos_prpt_properties.id
                             WHERE jos_prpt_properties.published = 1
                               AND jos_prpt_listingtype IN (1,3) 
                               AND jos_prpt_features.bedrooms >= 4 
                               AND views = 
                                   ( SELECT MIN(views)
                                       FROM jos_prpt_properties
                                     INNER
                                       JOIN jos_prpt_listingtype 
                                         ON jos_prpt_listingtype.id 
                                          = jos_prpt_properties.listing_type 
                                     INNER 
                                       JOIN jos_prpt_features 
                                         ON jos_prpt_features.property_id 
                                          = jos_prpt_properties.id
                                      WHERE jos_prpt_properties.published = 1
                                        AND jos_prpt_listingtype IN (1,3) 
                                        AND jos_prpt_features.bedrooms >= 4 
                                        AND views > p.views )
                          ) [COLOR="Red"]AS dumpty [/COLOR]) 
    INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id 
    INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id 
    INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id 
    INNER JOIN jos_prpt_features AS f ON p.id=f.property_id 
    INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id 
    INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id 
    INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC;

by the way, i think it is only fair to point out that after all the posts in this thread dealing with finding the previous and next links, and after i took the time to write the complex SQL that will provide them for you, it is silly of you to remove them from the SELECT clause

Ahh… okay I thought I could not understand somewhere the long query. Anyway its okay will see it.

Actually I am now busy with some other fixes in the project so not having quite enough time to study on the query you have suggested that’s why I just gave a quick try by removing them from SELECT clause after I get the error ‘Every derived table must have its own alias’ if can avoid the error if there were some typos because I really don’t think you have posted wrong query and I am sure that is because of some typos at your end. I don’t really mean to remove them intentionally without a reason.

I am really grateful to you (all all others here who have suggested) that you have tried to make me to go in right way. I will try to see this later when I will be free from other stuffs later today.

Thank you very much all once again.