CODEWITHSUNDEEP
mysqlsqlselectconditional-statements
xx77aBs
xx77aBs

Reputation: 4768

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

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 ?

Upvotes: 3

Views: 2757

Answers (2)

James Johnson
James Johnson

Reputation: 46067

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.

Upvotes: 2

Karolis
Karolis

Reputation: 9572

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.

Upvotes: 2

Related Questions