Hi Jim and Igor,
Here's basically what the schema looks like:
CREATE products (id, category_id, name, description)
CREATE items (id, product_id, part_number, name, price, buyable)
CREATE images (id, item_id, filename, position)
I'm grouping in this case because I only want the unique "products".
By joining "items", and "images" I get more than one unique product,
since a product has many items. So, I'm mis-using GROUP BY?
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);
Query without GROUP BY:
----------------------------------
SELECT products.name, images.filename
FROM products
INNER JOIN items ON items.product_id = products.id
LEFT JOIN images ON images.item_id = items.id
ORDER BY products.name, images.position DESC
Result:
----------
SQLite T-Shirt | sqlt-white.jpg
SQLite T-Shirt | sqlt-black.jpg
SQLite Long Sleeved Shirt | sqll-white.jpg
SQLite Long Sleeved Shirt | sqll-black.jpg
SQLite Long Sleeved Shirt | (Null)
Query with GROUP BY:
-----------------------------------
SELECT products.name, images.filename
FROM products
INNER JOIN items ON items.product_id = products.id
LEFT JOIN images ON images.item_id = items.id
GROUP BY products.id
ORDER BY products.name, images.position DESC
Result:
----------
SQLite T-Shirt | sqlt-black.jpg
SQLite Long Sleeved Shirt | (Null)
Desired Result:
----------------------
SQLite T-Shirt | sqlt-white.jpg
SQLite Long Sleeved Shirt | sqll-white.jpg
The reason why I'm doing this is, it allows me to show only photos for
the items that match the filter. So if you searched for "SQLT-WHT",
it would show the "sqlt-white.jpg" image. I'm not really working with
t-shirts, but rather vehicle specific products. I just wish I was
working with t-shirts, as I'd probably do this differently ;)
Is this best left for post-processing outside the database?
James
On Wed, Nov 10, 2010 at 12:39 PM, Igor Tandetnik <itandet...@mvps.org> wrote:
> James <ja...@icionline.ca> wrote:
>> Is this a fairly simple problem and solution? What would you search
>> for to find solutions to this?
>
> That rather depends on what the problem is, which as far as I can tell you've
> never explained. Show the schema of your tables, and a small sample of the
> data. Describe the expected result, show what you would expect the query to
> return given the sample data.
>
> 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
