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
