SQL - If table entry exists then

What I am wanting to do is if there is an en开发者_高级运维try in "player_shops" for the account ID then have a specific where clause, otherwise have no where clause. If that makes sense. Here is my code:

SELECT *,
       SUM(acc.gold_coins + shop.gold_coins) AS total_gold
  FROM accounts AS acc
     , player_shops AS shop
 WHERE acc.acc_id = shop.acc_id
 GROUP BY acc.acc_id
 ORDER BY total_gold DESC
 LIMIT 100

I want that WHERE clause to only be there IF the is a player_shop entry for the specified account ID.

Any help?

You better use JOIN.

SELECT *,
   SUM(acc.gold_coins + IF(shop.gold_coins IS NULL, 0, shop.gold_coins)) AS total_gold
FROM accounts AS acc
LEFT JOIN player_shops AS shop
ON acc.acc_id = shop.acc_id
GROUP BY acc.acc_id
ORDER BY total_gold DESC
LIMIT 100

I'm not sure about IF syntax. It depends on what db you use.

If you are using MS SQL try this:

WHERE acc.acc_id = COALESCE(shop.acc_id, acc.acc_id)

You should ALWAYS avoid using a Cartesian product in an SQL query whenever you can. Instead of joining the tables accounts and player_shops like that (with a comma) you should probably look into LEFT OUTER JOIN which might indirectly solve this problem for you as well.

First, SELECTing * when you select from more than one table or when you have a group by clause is meaningless and will issue an error only.

I think that you want here a LEFT JOIN:

 FROM accounts AS acc
 LEFT OUTER JOIN player_shops AS shop
   ON shop.acc_id = acc.acc_id

shop.acc_id will be filled with NULL if there is no record.