CODEWITHSUNDEEP
mysql
Ben
Ben

Reputation: 62356

Selecting another row based on column value

If I have the query below which selects a forum board, but the board table contains a column mirror_board_id. Is it possible for me to modify a basic select query to where if mirror_board_id > 0 that it will SELECT * FROM board WHERE board_id = mirror_board_id or something. Currently I'm using 2 queries to accomplish this, but that's a bit query intensive...

SELECT * FROM board WHERE category_id = 7 AND board_id = 0

update

SELECT * FROM board WHERE category_id = 7 AND
                            (
                                    (
                                        mirror_board_id > 0
                                        AND board_id = mirror_board_id
                                    )
                                OR board_id = 0
                            ) AND
                            display = 1 ORDER BY `order` ASC

This is returning the wrong boards in the list, it's not actually returning the mirror boards, but original boards where mirror_board_id > 0

Upvotes: 0

Views: 1803

Answers (1)

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171351

Logically, it is:

SELECT * 
FROM board 
WHERE (mirror_board_id > 0 and board_id = mirror_board_id) 
    or board_id = 0

This can be written more succinctly as:

SELECT * 
FROM board 
WHERE board_id = coalesce(mirror_board_id, 0)

Upvotes: 4

Related Questions