RE: FN-FORUM: Sql query help

date posted 14th January 2004 11:58

>Give us some sample data, and the results you expect to achieve.

Ok thanks, but sorry if I don't respond right away I've just trashed my
development server!

I want to display each supplier on the database in a table
In addition I want to display the number of items for that supplier and the
number of those items where the barcode field has been omitted

If a supplier has 0 items or no missing barcodes they still get shown in the
list

That tables look like this:

:products:

ItemCode (smallint auto_increment primary key)
Supplier_ID (smallint) key(mul)
ItemDescription (varchar) my test item
Barcode (bigint)

:supplier:

Supplier_ID (smallint auto_increment primary key)
Name
Address1

there are other fields and tables but they're not relevant

At the moment the test data is 14 suppliers and 2456 items

So item 123 for Supplier1 has a null entry for barcode
Supplier 2 has no items, all the other suppliers items all have barcodes

I want:

Name Address1 NumItems missiogBarcodes
sup1 bora bora 56 1
sup2 tenby 0 0
sup3 peru 175 0
sup4 peru 458 0
sup5 peru 321 0
etc etc

I'm quite happy to move the barcode field to it's own table if it will help.

Thanks
Ron