I think this might work for me, and provide good performance:
SELECT products.name, items.id,images.filename, images.featured
FROM products
INNER JOIN items ON items.product_id = products.id
LEFT JOIN images ON images.item_id = items.id
WHERE (images.featured = '1' OR images.featured IS NULL) AND
items.name = 'Black'
ORDER BY products.name
Doesn't allow me to control the ordering exactly, but at least it
would allow me to ensure that I get the first image I want when
needed. The OR images.featured IS NULL, would allow other
colors/images to replace the featured image when there is no featured
image found.
James
On Wed, Nov 10, 2010 at 4:24 PM, James <ja...@icionline.ca> wrote:
> Thanks Jim,
>
> The position is what I wanted to use to control the display order of
> the images. So yes, which should be first, second, third. Items can
> have more than one image. If you remove name='White' from your query,
> you'll see what I mean.
>
> I didn't want to rely on something like position=1, but instead a
> general "priority" where higher numbers are displayed first (or
> ascending where lower numbers are first, but then Null becomes a
> problem which comes before any number).
>
> Your example definitely sparked some new simpler thoughts though.
> Like whether I could make it work where I just specify an image as
> "featured".
>
> James
>
> On Wed, Nov 10, 2010 at 4:03 PM, Jim Morris <jmor...@bearriver.com> wrote:
>> If you would explain why/how the position value is significant that
>> might help.
>>
>> I fixed your pseudo SQL to run in SQLite Manager and I don't understand
>> from the
>> sample data what your trying to do. There is only one image per item.
>> Do you have multiple images per item and only want to return the first?
>>
>> A query like the following seem to yield reasonable results:
>> SELECT products.name, items.id,images.filename, images.position
>> FROM products
>> INNER JOIN items ON items.product_id = products.id
>> LEFT JOIN images ON images.item_id = items.id
>> WHERE items.name='White'
>> ORDER BY products.name ASC, images.position ASC
>> ;
>>
>> The cleaned up code is:
>>
>> CREATE TABLE products (id, category_id, name, description);
>> CREATE TABLE items (id, product_id, part_number, name, price, buyable);
>> CREATE TABLE images (id, item_id, filename, position);
>>
>> INSERT INTO products (id, category_id, name ) VALUES (1, 1, 'SQLite
>> T-Shirt');
>> INSERT INTO products (id, category_id, name ) VALUES (2, 1, 'SQLite Long
>> Sleeved Shirt');
>> INSERT INTO items (id, product_id, name) VALUES ('SQLT-WHT', 1, 'White');
>> INSERT INTO items (id, product_id, name) VALUES ('SQLT-BLK', 1, 'Black');
>> INSERT INTO items (id, product_id, name) VALUES ('SQLL-WHT', 2, 'White');
>> INSERT INTO items (id, product_id, name) VALUES ('SQLL-BLK', 2, 'Black');
>> INSERT INTO items (id, product_id, name) VALUES ('SQLL-BLU', 2, 'Blue');
>> INSERT INTO images (item_id, filename, position) VALUES
>> ('SQLT-WHT','sqlt-white.jpg', 2);
>> INSERT INTO images (item_id, filename, position) VALUES
>> ('SQLT-BLK','sqlt-black.jpg', 1);
>> INSERT INTO images (item_id, filename, position) VALUES
>> ('SQLL-WHT','sqll-white.jpg', 2);
>> INSERT INTO images (item_id, filename, position) VALUES
>> ('SQLL-BLK','sqll-black.jpg', 1);
>>
>>
>> On 11/10/2010 2:47 PM, James wrote:
>>> This will only display products which have items with images. I think
>>> I'm going to sit back and see if there's a simpler way to achieve what
>>> I'm trying to do. Maybe I'm going about this the wrong way, or I need
>>> to make some compromises.
>>>
>>> Thanks
>>>
>>> On Wed, Nov 10, 2010 at 3:01 PM, Igor Tandetnik<itandet...@mvps.org> wrote:
>>>> select name, filename from products p, images im
>>>> where im.item_id = (
>>>> select im2.item_id from items left join images im2 on (items.id =
>>>> im2.item_id)
>>>> where items.product_id = p.id and items.buyable
>>>> order by position desc limit 1)
>>>> order by name;
>>>>
>>>> --
>>>> Igor Tandetnik
>>>>
>>>>
>>>> _______________________________________________
>>>> sqlite-users mailing list
>>>> sqlite-users@sqlite.org
>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>
>>> _______________________________________________
>>> sqlite-users mailing list
>>> sqlite-users@sqlite.org
>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> _______________________________________________
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
