Join 3 Tables - distinct results

[Dave M]

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month. I know this can be
accomplished through a join, but I need some help. Thanks in advance.

Dave

[Brandon]

Can you give the table structures? This looks like an interesting one to
try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month. I know this can be
accomplished through a join, but I need some help. Thanks in advance.

Dave

[Brandon]

Can you give the table structures? This looks like an interesting one to
try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month. I know this can be
accomplished through a join, but I need some help. Thanks in advance.

Dave

[Brandon]

Can you give the table structures? This looks like an interesting one to
try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacturers have reported in the given month. I know this can be
accomplished through a join, but I need some help. Thanks in advance.

Dave

[Dave M]

mysql> desc no_report;
+----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+----------------+
| NRID | smallint(5) unsigned | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------+----------------------+------+-----+---------+----------------+
etc ...

mysql> desc widgets_a;
+----------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+----------------+
etc ...

mysql> desc widgets_b;
+----------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+----------------+
etc ...

Brandon wrote:

Can you give the table structures? This looks like an interesting one to
try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacture rs have reported in the given month. I know this can be
accomplishe d through a join, but I need some help. Thanks in advance.

Dave

[Dave M]

mysql> desc no_report;
+----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+----------------+
| NRID | smallint(5) unsigned | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------+----------------------+------+-----+---------+----------------+
etc ...

mysql> desc widgets_a;
+----------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+----------------+
etc ...

mysql> desc widgets_b;
+----------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+----------------+
etc ...

Brandon wrote:

Can you give the table structures? This looks like an interesting one to
try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacture rs have reported in the given month. I know this can be
accomplishe d through a join, but I need some help. Thanks in advance.

Dave

[Dave M]

mysql> desc no_report;
+----------------+----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+----------------+
| NRID | smallint(5) unsigned | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------+----------------------+------+-----+---------+----------------+
etc ...

mysql> desc widgets_a;
+----------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+----------------+
etc ...

mysql> desc widgets_b;
+----------------------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+----------------+
| id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+----------------+
etc ...

Brandon wrote:

Can you give the table structures? This looks like an interesting one to
try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacture rs have reported in the given month. I know this can be
accomplishe d through a join, but I need some help. Thanks in advance.

Dave

[Brandon]

I am going to assume you have a lookup table for the Manufacturer. I'm using
this common table to do the joins from. I also assumed that it will be more
helpfull to see the companyname rather than the ID, if not just change the
select. I created a forth table to test this called manid which has two
fields ID and companyname.

SELECT DISTINCT manid.companyna me
FROM manid LEFT JOIN no_report ON manid.id = no_report.manuf acturerid
LEFT JOIN widgets_a ON manid.id = widgets_a.manuf acturerid
LEFT JOIN widgets_b on manid.id = widgets_b.manuf acturerid
WHERE (no_report.orde rdate >='2003-08-01' AND no_report.order date
<='2003-08-31') OR
(widgets_a.orde rdate >='2003-08-01' AND widgets_a.order date <='2003-08-31')
OR
(widgets_b.orde rdate >='2003-08-01' and widgets_b.order date <='2003-08-31')

Let me know if this produces the desired results.

Brandon

"Dave M" <di******@eswis .net> wrote in message
news:bs******** ***@news.randor i.com...

mysql> desc no_report;
+----------------+----------------------+------+-----+---------+------------
----+ | Field | Type | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+------------
----+ | NRID | smallint(5) unsigned | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------+----------------------+------+-----+---------+------------
----+ etc ...

mysql> desc widgets_a;
+----------------------------+-------------+------+-----+---------+---------
-------+ | Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+---------
-------+ | id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+---------
-------+ etc ...

mysql> desc widgets_b;
+----------------------------+-------------+------+-----+---------+---------
-------+ | Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+---------
-------+ | id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+---------
-------+ etc ...

Brandon wrote:

Can you give the table structures? This looks like an interesting one to try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacture rs have reported in the given month. I know this can be
accomplishe d through a join, but I need some help. Thanks in advance.

Dave

[Brandon]

I am going to assume you have a lookup table for the Manufacturer. I'm using
this common table to do the joins from. I also assumed that it will be more
helpfull to see the companyname rather than the ID, if not just change the
select. I created a forth table to test this called manid which has two
fields ID and companyname.

SELECT DISTINCT manid.companyna me
FROM manid LEFT JOIN no_report ON manid.id = no_report.manuf acturerid
LEFT JOIN widgets_a ON manid.id = widgets_a.manuf acturerid
LEFT JOIN widgets_b on manid.id = widgets_b.manuf acturerid
WHERE (no_report.orde rdate >='2003-08-01' AND no_report.order date
<='2003-08-31') OR
(widgets_a.orde rdate >='2003-08-01' AND widgets_a.order date <='2003-08-31')
OR
(widgets_b.orde rdate >='2003-08-01' and widgets_b.order date <='2003-08-31')

Let me know if this produces the desired results.

Brandon

"Dave M" <di******@eswis .net> wrote in message
news:bs******** ***@news.randor i.com...

mysql> desc no_report;
+----------------+----------------------+------+-----+---------+------------
----+ | Field | Type | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+------------
----+ | NRID | smallint(5) unsigned | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------+----------------------+------+-----+---------+------------
----+ etc ...

mysql> desc widgets_a;
+----------------------------+-------------+------+-----+---------+---------
-------+ | Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+---------
-------+ | id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+---------
-------+ etc ...

mysql> desc widgets_b;
+----------------------------+-------------+------+-----+---------+---------
-------+ | Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+---------
-------+ | id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+---------
-------+ etc ...

Brandon wrote:

Can you give the table structures? This looks like an interesting one to try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacture rs have reported in the given month. I know this can be
accomplishe d through a join, but I need some help. Thanks in advance.

Dave

[Brandon]

I am going to assume you have a lookup table for the Manufacturer. I'm using
this common table to do the joins from. I also assumed that it will be more
helpfull to see the companyname rather than the ID, if not just change the
select. I created a forth table to test this called manid which has two
fields ID and companyname.

SELECT DISTINCT manid.companyna me
FROM manid LEFT JOIN no_report ON manid.id = no_report.manuf acturerid
LEFT JOIN widgets_a ON manid.id = widgets_a.manuf acturerid
LEFT JOIN widgets_b on manid.id = widgets_b.manuf acturerid
WHERE (no_report.orde rdate >='2003-08-01' AND no_report.order date
<='2003-08-31') OR
(widgets_a.orde rdate >='2003-08-01' AND widgets_a.order date <='2003-08-31')
OR
(widgets_b.orde rdate >='2003-08-01' and widgets_b.order date <='2003-08-31')

Let me know if this produces the desired results.

Brandon

"Dave M" <di******@eswis .net> wrote in message
news:bs******** ***@news.randor i.com...

mysql> desc no_report;
+----------------+----------------------+------+-----+---------+------------
----+ | Field | Type | Null | Key | Default | Extra
|
+----------------+----------------------+------+-----+---------+------------
----+ | NRID | smallint(5) unsigned | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------+----------------------+------+-----+---------+------------
----+ etc ...

mysql> desc widgets_a;
+----------------------------+-------------+------+-----+---------+---------
-------+ | Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+---------
-------+ | id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+---------
-------+ etc ...

mysql> desc widgets_b;
+----------------------------+-------------+------+-----+---------+---------
-------+ | Field | Type | Null | Key | Default |
Extra |
+----------------------------+-------------+------+-----+---------+---------
-------+ | id | int(11) | | PRI | NULL |
auto_increment |
| Date | date | YES | | NULL |
|
| ManufacturerID | smallint(6) | YES | | NULL |
|
| NumberUnits | int(11) | YES | | NULL |
|
| ContractNumber | text | YES | | NULL |
|
| OrderDate | date | YES | | NULL |
|
+----------------------------+-------------+------+-----+---------+---------
-------+ etc ...

Brandon wrote:

Can you give the table structures? This looks like an interesting one to try....

"Dave M" <di******@eswis .net> wrote in message
news:rh******** ****@news.rando ri.com...

All

I've got a database that keeps track of sales of widgets. Each company
that belongs to my organiztion is to report their widget sales or no
sales every month.

There are several different types of widgets. Not all companies sell or
report all types of widgets.

We want to report how many companies have reported or not reported their
sales (ie. x companies of a possible y companies have reported sales
this month - y will always be the same - lets say 5).

Because of the way that sales are input, "big widgets" are reported in 2
different tables called widgets_a and widgets_b. If they don't have any
sales to report, they still report and it goes into a table called
no_reports. Each table has a couple of common fields - ManufacturerID
and OrderDate.

I can search all of the tables individually to find if a manufacturer
has reported -

SELECT DISTINCT ManufacturerID FROM widgets_a WHERE OrderDate >=
'2003-06-01' AND OrderDate <= '2003-06-30';

but I want to search through the 3 tables and find how many distinct
manufacture rs have reported in the given month. I know this can be
accomplishe d through a join, but I need some help. Thanks in advance.

Dave