Hi Armer,
thanks a lot for your help ... it was very usefull!!! After understanding
the principles I wonder why I didn't think it up myself ... but then
solutions often appear obvious after a problem is solved.
Also I like your remarks on testing queries on paper first -- I'll see if I
can use that ...
And finally I noticed that I can find out how many invoices have been issued
per job using the <count> term. Thus making it easy to keep track about
monitions:
mysql> select jobs.*, count(invoices.invoice_id)
-> from invoices, jobs
-> where jobs.job_id = invoices.job_id
-> group by invoices.job_id
thanks again!! -- K:)
on 28/8/02 11:47 Uhr, Amer Neely at [EMAIL PROTECTED] wrote:
>> 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.
--
Kai Vermehr
eBoy Graphics
http://www.eboy.com
---------------------------------------------------------------------
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
