Update table causing troubles

Databases
1

Hi,

I am trying to query two db tables where I need the results of the first table and the results of the second table, but I need the results from the first table even if there is no match with the second table.

first tbale is a list of all images.
second table is a list of the images used in a specific gallery.

if an image from first table is not used in a gallery, i need to show the image data anyway but assign a value of 0 (zero) to the active status.

I have tried inner, left outer and right outer joins and am at a loss as to how to return the image data and a 1 or 0 for the active part.

here is my attempt. what am I overlooking or missing, please

bazz


SELECT SQL_CALC_FOUND_ROWS 
           i.id
         , i.image_name
         , i.caption
         , CASE WHEN ig.active IS NULL THEN 0
               ELSE 1 END 
    from images AS i
inner
      join image_galleries AS ig
       on ig.image_id = i.id
 WHERE ig.business_id = 360
     and ig.gallery_name = 'Breakfasts'

create statements:


CREATE TABLE image_galleries 
( business_id int(11) NOT NULL
, gallery_name varchar(32) NOT NULL default ''
, image_id int(11) NOT NULL
, active tinyint(4) NOT NULL default '0'
, PRIMARY KEY  (business_id,gallery_name,image)
, KEY gallery_header_fk (gallery_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE images 
( id int(11) NOT NULL auto_increment
, business_id int(11) NOT NULL
, image_name varchar(24) default NULL
, caption varchar(300) default NULL
, PRIMARY KEY  (id)
, KEY image_business_fk (business_id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

sample data


INSERT INTO `image_galleries` (`business_id`, `gallery_name`, `image_id`, `active`) VALUES
(360, 'Breakfasts', 20, 1),
(360, 'Breakfasts', 21, 1),
(360, 'Breakfasts', 26, 1);



(20, 360, 'image07.jpg', 'An example of our smoked salmon with scrambled egg and dill and lime, with toast and sliced tomato.'),
(21, 360, 'image08.jpg', 'Fresh fruit salad, with freshly squeezed pure orange juice.  Also shown is a portion of homemade jams and marmalade.'),
(26, 360, 'traditional_breakfast5.jpg', 'Traditonal Breakfast but mostly grilled instead of being fried. Made with selected, quality Sausage, Bacon, Tomato and Fried Egg; served with local breads - Potato Bread and Soda Bread.\\r\
See Menus for our sourcing policy.  ');
2

yes I got it :slight_smile:

using the case statement I restricted the results as required.

Thanks felgall for the left join help.

bazz

3

A LEFT JOIN does that. Currently you are using an INNER JOIN which specifically discards unmatched records from both tables.

4

I have already tried it with a left join and have set it like that again now. no dice. I notice that if I remove the AND from the where clause, i get back results whether they are in both tables or not.

However, this means that if I am looking at the gallery for interior shots (thumbnail and checkbox), where the checkbox shows if the particular image is in this gallery, I get back a ticked checkbox, if that image is used in ay gallery.

Maybe I should try to assign a vlue of 1 or 0 using the gallery name in the CASE statement?

bazz
ps sorry about the wrong title. I wasn’t able to change it after submission and it was already there from earlier when I had been going to post another question. However I resolved it.