James <ja...@icionline.ca> wrote:
> CREATE products (id, category_id, name, description)
> CREATE items (id, product_id, part_number, name, price, buyable)
> CREATE images (id, item_id, filename, position)
>
> INSERT INTO products VALUES (1, 1, 'SQLite T-Shirt');
> INSERT INTO products 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);
>
>
> Desired Result:
> ----------------------
> SQLite T-Shirt | sqlt-white.jpg
> SQLite Long Sleeved Shirt | sqll-white.jpg
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
