MySQL - 3 Table Join, COUNT DISTINCT widget cost

Problems with MySQL performing 3 table join and counting distinct widgets

[vgods]

I have the following 3 tables in my price comparison database:

Table1:
WidgetID
WidgetName

Table2:
WidgetID
WidgetColourID

Table3:
MerchantID
WidgetColourID
WidgetCost

I want to perform a 3 table join, and return a count of distinct WidgetName for various range bins of MIN(WidgetCost).

Here's the code I've hacked together:

$q = "SELECT (SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost < 100) AS count1,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 100 AND Table3.Cost < 150) AS count2,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 150 AND Table3.Cost < 200) AS count3,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 200 AND Table3.Cost < 250) AS count4,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 250 AND Table3.Cost < 300) AS count5,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 300 AND Table3.Cost < 350) AS count6,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 350 AND Table3.Cost < 400) AS count7,

(SELECT COUNT(DISTINCT Table1.WidgetName) FROM Table1, Table2, Table3 WHERE Table1.WidgetID = Table2.WidgetID AND Table2.WidgetColourID = Table3.WidgetColourID AND Table3.Cost > 400) AS count8";

The problem is:

a) My code is horrible, is there a more graceful way to write this, without so many SELECT statements?

b) My code doesn't quite work correctly, as there are many widgetCosts to 1 WidgetName (from different merchants), I only want the minimum widgetCost for that particular WidgetName to be counted. This usually works fine because of the DISTINCT, but if different widgetCosts happen to fall over 2 different range bins, they are counted twice, because of the seperate SELECT statements. One solution to this would be to have an extra column in my database for Table3 - a boolean to say if this merchant is the cheapest. My select statement could then only check WHERE BEST=1, any other solutions?

For example, my joined table:
WidgetID - WidgetName - WidgetColourID - MerchantID - WidgetCost
1 - Widget1 - BlackWidget1 - 1 - 90
1 - Widget1 - BlackWidget1 - 2 - 110

This one Widget is counted in both < 100 and 100 < x < 150 range bins (DISTINCT doesn't work because of seperate SELECT statements).

I want my end product to be:

< £100 (count)
£100 to £150 (count)
£150 to £200 (count)
...

etc.

Just like you find on Amazon.

Hope I explained it OK, thanks in advance.