>Subject: job database with invoicing
> Date: Tue, 27 Aug 2002 22:05:43 +0200
> From: Kai Vermehr <[EMAIL PROTECTED]>
> To: MySQL List <[EMAIL PROTECTED]>
>
> I'm building a job database with simple invoicing. I have one table called
> JOBS and one called INVOICES. In INVOICES there's a foreign key column
> called job_id referencing INVOICES to JOBS.job_id.
>
> in a simplified way it looks like this:
> (there are a lot of other columns of course)
>
> --------
>
> table JOBS (
> job_id int not null auto_increment,
> jobname char(30),
> primary key job_id
> )
>
> and
>
> table INVOICES (
> invoice_id int not null auto_increment,
> job_id int(11),
> foreign key job_id,
> primary key invoice_id
> )
>
> --------
>
> Would this make sense if in the future I want to find out:
>
> #1 which jobs have and have not been invoiced
> #2 what invoices are linked to specific job
>
> What would the MySQL statement look like? I've tried to find out but I'm not
> sure if the relations between both tables make sense at all ...
>
> thanks for any help! K:)
>
I'm in the middle of writing my own accounts receivable db so this
caught my eye.
My first question though concerns your first question. If a job hasn't
been invoiced, there won't / shouldn't be an entry for 'job_id' in the
INVOICES table, right?
When I start work on a new db, I usually put some dummy data down on
paper in a grid and do all my testing there. Once I have an answer to a
query, I can then test it against the real db by comparing results.
The dummy data I used:
mysql> select * from invoices;
+------------+--------+
| invoice_id | job_id |
+------------+--------+
| 701 | 1 |
| 702 | 2 |
| 703 | 1 |
| 704 | 3 |
| 705 | 1 |
| 706 | 3 |
| 707 | 2 |
| 708 | 5 |
| 709 | 1 |
| 710 | 2 |
+------------+--------+
10 rows in set (0.00 sec)
mysql> select * from jobs;
+--------+----------+
| job_id | job_name |
+--------+----------+
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |
+--------+----------+
5 rows in set (0.00 sec)
*** Note that job_id 4 (D) does not have an entry in the invoices table,
therefore it hasn't been invoiced.
In this case your first question can be translated into something like:
Q: what jobs have been invoiced
mysql> select invoices.*, jobs.* from invoices,jobs
-> where jobs.job_id=invoices.job_id
-> group by invoices.job_id;
+------------+--------+--------+----------+
| invoice_id | job_id | job_id | job_name |
+------------+--------+--------+----------+
| 701 | 1 | 1 | A |
| 702 | 2 | 2 | B |
| 704 | 3 | 3 | C |
| 708 | 5 | 5 | E |
+------------+--------+--------+----------+
4 rows in set (0.00 sec)
Q; what jobs have not been invoiced?
mysql> select jobs.*,invoices.* from jobs left join invoices on
-> jobs.job_id=invoices.job_id;
+--------+----------+------------+--------+
| job_id | job_name | invoice_id | job_id |
+--------+----------+------------+--------+
| 1 | A | 701 | 1 |
| 1 | A | 703 | 1 |
| 1 | A | 705 | 1 |
| 1 | A | 709 | 1 |
| 2 | B | 702 | 2 |
| 2 | B | 707 | 2 |
| 2 | B | 710 | 2 |
| 3 | C | 704 | 3 |
| 3 | C | 706 | 3 |
| 4 | D | NULL | NULL |
| 5 | E | 708 | 5 |
+--------+----------+------------+--------+
11 rows in set (0.11 sec)
or, perhaps a better query ...
mysql> select jobs.*,invoices.*
-> from jobs left join invoices on jobs.job_id=invoices.job_id
-> where invoices.job_id is null;
+--------+----------+------------+--------+
| job_id | job_name | invoice_id | job_id |
+--------+----------+------------+--------+
| 4 | D | NULL | NULL |
+--------+----------+------------+--------+
1 row in set (0.00 sec)
Q: what invoices are linked to specific job
mysql> select invoices.*,jobs.* from invoices,jobs
-> where job_name like '%A%'
-> and invoices.job_id=jobs.job_id
-> order by invoice_id;
+------------+--------+--------+----------+
| invoice_id | job_id | job_id | job_name |
+------------+--------+--------+----------+
| 701 | 1 | 1 | A |
| 703 | 1 | 1 | A |
| 705 | 1 | 1 | A |
| 709 | 1 | 1 | A |
+------------+--------+--------+----------+
4 rows in set (0.05 sec)
That should get you on your way.
--
/* All outgoing email scanned by Norton Antivirus 2002 */
Amer Neely, Softouch Information Services
W: www.softouch.on.ca
E: [EMAIL PROTECTED]
V: 519.438.5887
Perl | PHP | MySQL | CGI programming for all data entry forms.
"We make web sites work!"
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
