CODEWITHSUNDEEP
mysql
Michiel
Michiel

Reputation: 1006

Combine two SELECTS from same table

I have an table with projects in them:

My projects-page shows the current project and a preview of the next three projects. For fetching the next three projects, I would like to use a clean MySQL query. Of course, if the person is on one of the last projects, there are not 3 more projects left to show.

So it then needs to show the first one again.

Basically Im trying to combine these two statements:

SELECT *
FROM projects
WHERE ordering > {currentProjectOrdering}
ORDER BY ordering ASC

and

SELECT *
FROM projects
WHERE ordering > 0
ORDER BY ordering ASC

and also

LIMIT 0,3

In a nutshell: get the next three records with higher ordering than the current project, if (some of) these do not exist, start from ordering = 1.

Assuming there are 10 projects: Project 1 shows 2,3 and 4 Project 2 shows 3,4 and 5 ... Project 9 shows 10, 1 and 2

Upvotes: 0

Views: 527

Answers (3)

Shawn Campbell
Shawn Campbell

Reputation: 193

Something Like this should work (assuming you limiting by just projects that person is working on):

     SELECT * FROM projects WHERE person = person_id && (ordering > 0 && COUNT(id) < 4) 
|| (ordering >= currentProjectOrdering && COUNT(id) >= 4)) ORDER BY ordering LIMIT 0,3;

Upvotes: 0

user1166147
user1166147

Reputation: 1610

I'm curious about this. I don't have mysql and can't test, but I don't think you are going to get the 3 records you want in these examples - if you are at the end of the list and need to grab 1 or 2 from the beginning. I'm playing with TSQL - so there is no LIMIT Hence the TOP.

I'm also curious if anyone can do it better.

SELECT  TOP 3 
    CASE
    WHEN ORDERING >= {currentProjectOrdering} THEN ORDERING
    ELSE {currentProjectOrdering} - ORDERING END AS DISPLAYORDER,*
FROM SO_projects  
WHERE (ordering >= {currentProjectOrdering} AND ordering < {currentProjectOrdering}+ 3 ) OR ordering < 3
ORDER BY DISPLAYORDER DESC

Can't promise as I have no idea about MYSQL, but here is my try. In T-SQL TOP returns the specified number of recs (3 in this case) depending on the ORDER BY. There is no equivocation to the 0 in the 0,3 part (I wish) of LIMIT. I googled and it looks like the CASE stuff should be pretty similar. You may be able to get rid of the > and < because of how LIMIT works. The logic works on SqlServer:

SELECT  
    CASE
    WHEN ORDERING >= {currentProjectOrdering} THEN ORDERING
    ELSE {currentProjectOrdering} - ORDERING END AS DISPLAYORDER,*
FROM SO_projects  
WHERE (ordering >= {currentProjectOrdering} AND ordering < {currentProjectOrdering} + 3    ) OR ordering < 3
ORDER BY DISPLAYORDER DESC 
LIMIT 0,3

Upvotes: 0

Fahim Parkar
Fahim Parkar

Reputation: 31647

If you want data from first condition AND second condition use

SELECT *
FROM projects
WHERE ordering > {currentProjectOrdering} AND ordering > 0
ORDER BY ordering ASC
LIMIT 0,3

If you want data from first condition OR second condition use

SELECT *
FROM projects
WHERE ordering > {currentProjectOrdering} OR ordering > 0
ORDER BY ordering ASC
LIMIT 0,3

Upvotes: 2

Related Questions