CODEWITHSUNDEEP
sqlmysql
Chuck
Chuck

Reputation: 908

MySQL conditional SELECT statement

If there are records that have a field containing "X", return them, else return a random record.

How the heck do you do this?

Upvotes: 1

Views: 1030

Answers (3)

Pavlo Svirin
Pavlo Svirin

Reputation: 508

Well, here is my example based on mysql.users table:

First, non existing records:

mysql> SELECT * FROM (select user, 1 as q from user where user like '%z' union all (select user, 0 from user limit 1)) b WHERE q=(SELECT CASE WHEN EXISTS(select user, 1 as q from user where user like '%z' ) THEN 1 ELSE 0 END);

+--------+---+
| user   | q |
+--------+---+
| drupal | 0 |
+--------+---+

1 row in set (0.00 sec)

Then, existing:

mysql> SELECT * FROM (select user, 1 as q from user where user like '%t' union all (select user, 0 from user limit 1)) b WHERE q=(SELECT CASE WHEN EXISTS(select user, 1 as q from user where user like '%t' ) THEN 1 ELSE 0 END);

+------------------+---+
| user             | q |
+------------------+---+
| root             | 1 |
| root             | 1 |
| debian-sys-maint | 1 |
| root             | 1 |
+------------------+---+

4 rows in set (0.00 sec)

Maybe it will be useful, or maybe someone will be able to rewrite it in better way.

Upvotes: 0

pedromarce
pedromarce

Reputation: 5669

Just the bit to select a random record would be very difficult and highly unefficient on large tables in mysql, in this website you can find one script to do it, it should be trivial to add your condition for 'x' and get the functionality you need.

Upvotes: 0

dnagirl
dnagirl

Reputation: 20446

This is best done with 2 queries. The first returns the records where field='x'. If that's empty, then do a query for a random record with field!='x'. Getting a random record can be very inefficient as you'll see from the number of "get random record" questions on SO. Because of this, you really only want to do it if you absolutely have to.

Upvotes: 1

Related Questions