By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,687 Members | 1,989 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,687 IT Pros & Developers. It's quick & easy.

Join 3 Tables - distinct results

P: n/a
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

Jul 19 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
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.randori.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

Jul 19 '05 #2

P: n/a
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.randori.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

Jul 19 '05 #3

P: n/a
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.randori.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

Jul 19 '05 #4

P: n/a
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.randori.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



Jul 19 '05 #5

P: n/a
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.randori.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



Jul 19 '05 #6

P: n/a
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.randori.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



Jul 19 '05 #7

P: n/a
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.companyname
FROM manid LEFT JOIN no_report ON manid.id = no_report.manufacturerid
LEFT JOIN widgets_a ON manid.id = widgets_a.manufacturerid
LEFT JOIN widgets_b on manid.id = widgets_b.manufacturerid
WHERE (no_report.orderdate >='2003-08-01' AND no_report.orderdate
<='2003-08-31') OR
(widgets_a.orderdate >='2003-08-01' AND widgets_a.orderdate <='2003-08-31')
OR
(widgets_b.orderdate >='2003-08-01' and widgets_b.orderdate <='2003-08-31')

Let me know if this produces the desired results.

Brandon

"Dave M" <di******@eswis.net> wrote in message
news:bs***********@news.randori.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.randori.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


Jul 19 '05 #8

P: n/a
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.companyname
FROM manid LEFT JOIN no_report ON manid.id = no_report.manufacturerid
LEFT JOIN widgets_a ON manid.id = widgets_a.manufacturerid
LEFT JOIN widgets_b on manid.id = widgets_b.manufacturerid
WHERE (no_report.orderdate >='2003-08-01' AND no_report.orderdate
<='2003-08-31') OR
(widgets_a.orderdate >='2003-08-01' AND widgets_a.orderdate <='2003-08-31')
OR
(widgets_b.orderdate >='2003-08-01' and widgets_b.orderdate <='2003-08-31')

Let me know if this produces the desired results.

Brandon

"Dave M" <di******@eswis.net> wrote in message
news:bs***********@news.randori.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.randori.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


Jul 19 '05 #9

P: n/a
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.companyname
FROM manid LEFT JOIN no_report ON manid.id = no_report.manufacturerid
LEFT JOIN widgets_a ON manid.id = widgets_a.manufacturerid
LEFT JOIN widgets_b on manid.id = widgets_b.manufacturerid
WHERE (no_report.orderdate >='2003-08-01' AND no_report.orderdate
<='2003-08-31') OR
(widgets_a.orderdate >='2003-08-01' AND widgets_a.orderdate <='2003-08-31')
OR
(widgets_b.orderdate >='2003-08-01' and widgets_b.orderdate <='2003-08-31')

Let me know if this produces the desired results.

Brandon

"Dave M" <di******@eswis.net> wrote in message
news:bs***********@news.randori.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.randori.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


Jul 19 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.