Hi Patrick!
> I need:
>
> acc.name, acc.phone, acc.acctno, sales1.amt as mo1sales, sales2.amt as
> mo2sales, (sales1.amt - sales2.amt) as diff SORT by diff
>
> I need all data in sales1 and sales2, but only for records from
> acc that are in either/both sales1, sales2.
>
> I tried:
> select .... from acc,sales1,sales2 where acc.acctno=sales1.acctno and
> acc.acctno=sales2.acctno
> I got only records which were in all three tables.
>
> I looked at using join, but can not determine which style of JOIN I need,
> nor how to write the JOIN statement. Can someone please help?
The JOIN you're asking for is the "LEFT JOIN". Let's compare straight
JOIN and the LEFT JOIN
SELECT * FROM a, b WHERE a.id=b.id
----------------------------------
* Will return records which are in both a and b tables.
SELECT * FROM a LEFT JOIN b ON a.id=b.id
--------------------------------------
* Will return ALL records from a, and those of b which are also in a.
(Note the non-existence of a WHERE clause)
Given that, we can start doing your query:
1)
SELECT * FROM acc
LEFT JOIN sales1 ON acc.acctno=sales1.acctno
LEFT JOIN sales2 ON acc.acctno=sales2.acctno
If you have this data:
acc sales1 sales2
---- ------- -------
1 1 1
2 2 3
3
4
The above query will return the following:
acc.acctno sales1.acctno sales2.acctno
----------- -------------- -------------
1 1 1
2 2 NULL
3 NULL 3
4 NULL NULL
That is, ALL the rows from a, and either sales1.acctno or sales2.acctno.
You don't clearly state if you want the row from acc.acctno ID 4 in the
result, but in case you don't, let's modify our query.
2)
SELECT * FROM acc
LEFT JOIN sales1 ON acc.acctno=sales1.acctno
LEFT JOIN sales2 ON acc.acctno=sales2.acctno
WHERE sales1.acctno IS NOT NULL OR sales2.acctno IS NOT NULL
You see, this WHERE clause is even "human readable", we want the rows
which sales1.acctno is not null OR sales2.acctno is not null.
Anyway, you say you want to do a (sales1.amt - sales2.amt). As far as I
can see, this is a calculation of the sales grow, that is, the difference
between each month sales. BEWARE, every calculation in which NULL is
involved, becomes NULL. For example:
a) 1 - NULL = NULL
b) NULL - 10 = NULL
c) NULL * 20000 = NULL
You see, NULL is a killer. :-D You can get rid of this using some of the
MySQL functions like IF_NULL.
The effect of this behaviour is getting erroneous results where a
customer don't have a sale in a particular month. Let's see:
If "sales1.amt=NULL" and "sales2.amt=1000" then "(sales2.amt -
sales1.amt) == NULL" which is, obviously, erroneous for your pourposes of
getting an increment of 1000 in the sales.
I hope this helped!
Cheers,
Jose Miguel.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
