CODEWITHSUNDEEP
mysql
mark
mark

Reputation: 1809

selecting from mysql table using an expression

I'm using Mysql and I'm stuck on what I hope is a simple problem. I need to select data from a table if one condition is true or another condition is true.

One select I tried returns data, but a lot more data than it should. Indeed the table contains just 66 records however my query is returning 177 records. I'm misunderstanding something.

I need to select data if ['city' is equal to a value and 'type' is golden] or 'type' is within a category called 'charms'

I've tried this query

SELECT b.* 
FROM bubbles b, bubble_types bt 
WHERE 
  b.city = 10 
  AND b.type = 'golden' 
  OR bt.category = 'charm' 
  AND bt.type = b.type;

and this one (which doesn't work at all but may be closer to the mark?)

SELECT b.* 
IF(b.city = 10, b.type = 'golden'), 
IF(bt.category = 'charm', bt.type = b.type) 
FROM bubbles b, bubble_types bt;

Hopefully what I want makes sense?

I should get about 10 rows from the 66 of those bubbles in city 10 that are 'golden', or those bubbles whose type field puts them in category 'charm'.

Thanks;

edit sample table data for bubble_types:

+----+----------+------------+ 
| id | category | type       | 
+----+----------+------------+ 
| 1  | bubble   | golden     | 
| 2  | charm    | teleport   | 
| 3  | charm    | blow       | 
| 4  | badge    | reuser     | 
| 5  | badge    | winner     | 
| 6  | badge    | loothunter | 
| 7  | charm    | freeze     | 
| 8  | badge    | reuser     | 
| 9  | badge    | winner     | 
| 10 | badge    | loothunter | 
+----+----------+------------+ 


mysql> describe bubbles;
+-------------+---------------------+------+-----+---------+----------------+
| Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| city        | bigint(20)          | YES  |     | NULL    |                |
| type        | varchar(32)         | YES  |     | NULL    |                |
| taken_by    | bigint(20)          | YES  |     | NULL    |                |
| taken_time  | bigint(20)          | YES  |     | NULL    |                |
| label       | varchar(256)        | YES  |     | NULL    |                |
| description | varchar(16384)      | YES  |     | NULL    |                |
| created     | datetime            | YES  |     | NULL    |                |
+-------------+---------------------+------+-----+---------+----------------+

Upvotes: 0

Views: 63

Answers (1)

Chris Trahey
Chris Trahey

Reputation: 18290

You are so close! Take the "WHERE"-ness of your first go with the parenthesis of your second (and add an appropriate ON clause to your JOIN): 

SELECT b.* 
FROM bubbles b
   JOIN bubble_types bt 
   ON b.type = bt.type
WHERE 
  (b.city = 10 AND b.type = 'golden')
  OR 
  (bt.category = 'charm' AND bt.type = b.type);

The devil is in the details of associativity of AND and OR in the where clauses. When in doubt, use parenthesis to make your intentions explicit.

Upvotes: 2

Related Questions