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
