Danilo Cicerone wrote:
> table appx stores item's(table itx) quantity load for each user (table
> subj). I'd to know how many items each user has:
>
> Paul|Box|3
> Paul|Letter|0
> Paul|Pen|0
> John|Box|0
> John|Letter|4
> John|Pen|0
>
> I tried:
>
> select sub_descr, itx_descr, app_load from subj
> left outer join appx on sub_id = app_ref_sub
> left outer join itx on app_ref_itx = itx_id;
>
> but it returns only:
>
> Paul|Box|3
> John|Letter|4
Your first LEFT includes users that do not have any items.
Your second LEFT includes quantity loads for which no item exists.
What you actually want is all combinations of users and items. This
is a cross join:
SELECT sub_descr,
itx_descr,
IFNULL(app_load, 0)
FROM subj
CROSS JOIN itx
LEFT JOIN appx ON sub_id = app_ref_sub AND
itx_id = app_ref_itx
Regards,
Clemens
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
