SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    table join giving trouble

    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

    Code MySQL:
    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:

    Code MySQL:
    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

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

    Code MySQL:
    (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\nSee Menus for our sourcing policy.  ');
    Last edited by IBazz; Jun 27, 2010 at 19:37. Reason: I stupidly wrote the wrong title when earlier compiling a different question, which I later resolved.

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, NSW, Australia
    Posts
    16,863
    Mentioned
    25 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by IBazz View Post
    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.
    A LEFT JOIN does that. Currently you are using an INNER JOIN which specifically discards unmatched records from both tables.
    Stephen J Chapman

    javascriptexample.net, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">

  3. #3
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes I got it

    using the case statement I restricted the results as required.

    Thanks felgall for the left join help.

    bazz


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •