Turn 2 Select Simple into 1

Asked

Viewed 75 times

0

I have the following table PLAYLIST where I have the columns VIDEO and STATUS:

VIDEO | STATUS

The column status varies between 1 and 0 being 1 for new videos and 0 for videos that have already been watched.

I’m currently performing two select:

SELECT * FROM playlist WHERE status='1';
if result > 0  { 
   echo ok
} else {
 SELECT * FROM playlist WHERE status='0'
}

You can make this query using only in 1 SELECT? preferred without using php?

My goal is to prioritize videos that have not been viewed.

php sql
  • 2

    Just take the where doesn’t solve? has some problem?

  • But he wants the remaining result, where the status is 0. It is not ?

  • and much like if php so that wanted to sql ,

  • 1

    You want to take the data from the playlist table that the status is 1 and if the result is empty (no line) grab all the status is 0?

  • I believe that status can only have the values 0 and 1; so you really wouldn’t need the where, as @rray said.

  • @Guilhermecostamilam yes exactly that

  • the user when posting the video it already goes with the status=1 , when watching the update video in status and marks the video as 0 = already displayed , and select is so that if all the videos have already been shown it will run those that have status=0

  • I don’t know what the purpose of the code is but one option is count() with group by the return has the column status and how often (total) he appeared.

  • Why not just use the last query then? SELECT * FROM playlist WHERE status='0'

  • @rLinhares rray purpose in the above comment, basically I want to prioritize the new videos that was inserted

  • 1

    If you just want to prioritize can use ORDER BY status DESC, so those with status 1 will come first, but if there can’t appear those with status 0 if there are any with status 1, take a look at my answer, I believe that’s what you’re looking for

  • 1

    He killed the puzzle by the status will give right in 1 query, now only tells me how to sort by ID and STATUS at the same time ORDER BY ID.STATUS DESC?

  • Use ORDER BY status DESC it will sort by status and then by id, because in the bank the natural order is by id, but if you want you can use ORDER BY status DESC, id

  • @Guilhermecostamilam put there as an answer for me to score

Show 9 more comments

1 answer

1


I believe that solves:

SELECT * FROM playlist WHERE
CASE
    WHEN (SELECT COUNT(*) FROM playlist WHERE status = 1) = 0 
    THEN status = 0
    ELSE status = 1
END

If the count of playlist with status = 1 for 0 makes a search with status = 0 otherwise, make a search with status = 1

But if you just want to prioritize you can use ORDER BY status DESC, so those with status 1 will come first

  • got it right is that same already eliminated php, but continues 2 SELECT.

  • Anyway it will be necessary a select to know if there is or not data with status = 1, and another that will return what you need

  • your comment on the question killed the riddle

Browser other questions tagged php sql

You are not signed in. Login or sign up in order to post.