Conditional SQL SELECT - when return set is empty, execute another SELECT?

Question

I have webpage that has its "pages/articles" stored in a MySQL database. It also has a feature to show a different side menu for different pages. All that (menus, menu's items) is stored in DB.

Here's my SQL for getting all menu items for current page:

SELECT * 
FROM menu_items 
   JOIN pages 
    ON menu_items.menu_id=pages.right_menu_id 
WHERE pages.link = "some_link"

and it works.

What I want is, when this query returns an empty set, to execute another query and get its result instead. Is it possible ?? If query mentioned above is empty, I would like to get result of this query:

SELECT * 
FROM menu_items 
WHERE menu_id=2;

Is it possible, or should I just do it in PHP ?

You could find a way to do it (with union, for example, or a subquery) but I think you really ought to just do it in PHP. — derobert
– derobert, Commented Sep 27, 2011 at 16:37

James Johnson · Accepted Answer · 2011-09-27 17:03:49Z

2

You could use the EXIST function to test whether the current page has any links:

IF EXISTS(SELECT menu_id FROM menu_items JOIN pages ON menu_items.menu_id = pages.right_menu_id WHERE pages.link = "some_link") THEN

    SELECT   * 
    FROM     menu_items 
             JOIN pages 
                 ON menu_items.menu_id=pages.right_menu_id 
    WHERE    pages.link = "some_link" 

ELSE

    SELECT   * 
    FROM     menu_items 
             JOIN pages 
                 ON menu_items.menu_id=pages.right_menu_id 
    WHERE    menu_id = 2

END IF

You could also try something like this:

DECLARE @LinkCount INT
SELECT @LinkCount = COUNT(*) FROM menu_items JOIN pages ON menu_items.menu_id = pages.right_menu_id WHERE pages.link = "some_link"

SELECT   * 
FROM     menu_items 
         JOIN pages 
             ON menu_items.menu_id=pages.right_menu_id 
WHERE    (@LinkCount > 0 AND pages.link = "some_link") OR (@LinkCount = 0 AND menu_id = 2)

There might be more elegant ways of doing this, but hope this helps.

edited Sep 27, 2011 at 17:03

answered Sep 27, 2011 at 16:45

James Johnson

46.1k8 gold badges75 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

xx77aBs Over a year ago

Thanks ! It's good to know how it can be done, but I think PHP solution is more elegant and readable, so I'll implement it in PHP ;)

Karolis · Accepted Answer · 2011-09-27 17:02:56Z

2

Perhaps this is not the most elegant way:

SELECT * 
FROM 
    menu_items 
    JOIN pages ON menu_items.menu_id = pages.right_menu_id 
WHERE pages.link = "some_link"

UNION ALL

SELECT *, NULL, NULL, NULL 
FROM menu_items 
WHERE 
    menu_id = 2 AND 
    NOT EXISTS (
        SELECT * 
        FROM 
            menu_items 
            JOIN pages ON menu_items.menu_id = pages.right_menu_id 
        WHERE pages.link = "some_link"
    )

Note: Number of NULLs should be the same as number of columns in table pages.

answered Sep 27, 2011 at 17:02

Karolis

9,59231 silver badges41 bronze badges

1 Comment

xx77aBs Over a year ago

Thanks :D But this just scares me :D

Collectives™ on Stack Overflow

Conditional SQL SELECT - when return set is empty, execute another SELECT?

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related