Peter Lauri wrote:
[snip "Chris"]
If you want multiple customers to be associated with each issue you need
3 tables:
create table customers (customerid int auto_increment primary key,
customername varchar(255));
create table issues (issueid int auto_increment primary key, issuetitle
varchar(255));
create table customer_issues (issueid int, customerid int);
then you can do:
select * from
customers c, issues i, customer_issues ci
where
c.customerid=ci.customerid AND
ci.issueid=i.issueid;
[/snip]
Is there not a better way to do that? What will happen there is that a large
result set will be created because when you just do "select * from
customers c, issues i, customer_issues ci" it will be like the inner product
from all these tables, and then just choosing the right ones.
If the table C have 1000 records, issues 5000 and customer_issues 15000 you
would end up with a 75,000,000,000 rows large results set, that would not be
so sweet, would it?
The 'where' clause cuts that down to only matching records between the
tables. Without the where, you'd end up with lots of rows but with the
where it will be fine.
Of course make sure all parts of the where clause are appropriately
indexed (one on c.customerid, one on i.issueid, one on ci.customerid and
ci.issueid)
That query should be the same as this one:
select * from
customers c
inner join customer_issues ci on (c.customerid=ci.customerid)
inner join issues i on (ci.issueid=i.issueid)
;
I don't like writing my queries that way because the order of the joins
needs to be very specific and as you add more tables, getting the order
wrong can make things slow (and hard to track down).
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
