Query pointer please

Databases
1

Hi,

I am querying the table of images per gallery. I want to get those of category ‘a’ but if there are none in that category to get those in cat ‘b’.

Not sure if I should use a case statement or maybe to join on the table twice looking for a non-match. maybe there is a simpler way that seems inadequate right now, to me. (bit like meself :frowning: )


CREATE TABLE image_galleries (
  business_id int(11) NOT NULL,
  gallery_category varchar(64) collate utf8_unicode_ci NOT NULL default 'Main Photographic Gallery',
  gallery_name varchar(32) collate utf8_unicode_ci NOT NULL default '',
  image_id int(11) NOT NULL,
  sequence_number int(11) default NULL,
  active tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (business_id,gallery_category,gallery_name,image_id),
  KEY gallery_header_fk (business_id,gallery_name),
  KEY galerry_images_fk (image_id),
  KEY business_id (business_id,gallery_category,gallery_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sample data:


business_id | gallery_cat | gallery_name | image_id | seq_no | active_io |
| 170 	    |  secondary  |  food        |   810    |    1   | 	1        |
| 170       | secondary   | food         |   812    |NULL    |  0        |
| 170       | secondary   |  general     |   812    |	1    |  1        |
| 170       | secondary   |  general     |   814    |	2    |  1        |

so my query is like this right now:


select 
  image_id
from image_galleries AS ig
where business_id = '170'
and gallery_name = 'secondary'
and gallery_name = 'food'
and active_io = 1 
order by seq_no

I need to get the food gallery images but if there is none, to get the (default) general images.

bazz

2

maybe, I should just get all the images and post-process them into whatever I need? total of about 10 images.

3

Oh, I just found your response, when I came back - no email notification :frowning:

Thanks rudy. that AND clause was wrong coz it should have been an OR but even then, the query was still wrong.

I’ll try your pseudo code as the basis for what is actually, a bigger query.

bazz

4

this –

and gallery_name = 'secondary'
and gallery_name = 'food'

is gonna return 0 rows forever, because a single column value on any given row cannot be equal to two different things at the same time

how about this (pseudocode) –

SELECT 1 AS sortkey
     , stuff
  FROM image_galleries 
 WHERE gallery_name = 'food'
UNION ALL
SELECT 2
     , stuff
  FROM image_galleries 
 WHERE gallery_name = 'secondary'
ORDER
    BY sortkey
     , stuff