List:General Discussion« Previous MessageNext Message »
From:André Hänsel Date:September 30 2006 2:34am
Subject:Re: Joining *3* tables
View as plain text  
> -----Ursprüngliche Nachricht-----
> Von: Renito 73 [mailto:renito73@stripped] 
> Gesendet: Samstag, 30. September 2006 04:20
> An: mysql@stripped
> Betreff: Joining *3* tables
> 
> Hello list

Hello Mr 73,

> I have a large database of contacts, but since not all fields 
> are used I 
> decided to separate all information in 3 tables to save space 
> like this:
> 
> DB_ADDRESS
> id int
> address char(128)
> ... three columns more ...
> 
> DB_COMPANY
> id int
> company char(64)
> 
> DB_LISTS
> id int
> list char(16)
> 
> Not all addresses have necesarily a company name, so it is 
> separate in a table 
> DB_COMPANY. Some other addresses are clasified in lists by 
> category and they 
> are grouped in DB_LISTS table.

Obviously you have a performant server (otherwise you would not accept the
performance loss of a join) and are very short on space. So simply use
VARCHAR columns, they take up only as much space as their content and are
yet faster than a join.

> If I want a report with address and company name pairs I use:
> 
> select address,company from DB_ADDRESS left join DB_COMPANY on 
> DB_ADDRESS.id=DB_COMPANY.id where DB_COMPANY.id is not null;
> 
> If I want a report of address belonging to a certain category:
> 
> select address,list from DB_ADDRESS left join DB_LISTS on 
> DB_ADDRESS.id=DB_LISTS.id where DB_LISTS.id is not null and 
> DB_LISTS.list="providers";
> 
> BUT... If I want to generate a report with address, company and list 
> (category) how can I join the three tables with a single 
> query? or should I 
> first generate a temporal table with the result of the first 
> join and then a 
> second one joining the third table?

Yes, but remember to drop all indexes from all tables. You can even remove
most of the memory from your machine if you want it extra slow. ;-)

You can chain as many joins as you want:
SELECT *
FROM db_address
JOIN db_lists USING (id)
JOIN db_company USING (id)
WHERE ...

> Thanks for your comments

Probably you should read through the MySQL doc once again before you proceed
with your project.

Regards,
André

Thread
comparing two databasesSteve Buehler28 Sep
  • Re: comparing two databasesDan Nelson28 Sep
    • Joining *3* tablesRenito 7330 Sep
      • Re: Joining *3* tablesAndré Hänsel30 Sep
  • Re: comparing two databasesCory Robin28 Sep
  • Re: comparing two databasesPeter Brawley28 Sep
    • RE: comparing two databasesAleksander Dye29 Sep
  • Re: comparing two databasesMartijn Tonies29 Sep
  • Re: comparing two databasesmark addison29 Sep
RE: comparing two databasesJohn Trammell28 Sep
RE: comparing two databasesAndrew Braithwaite29 Sep
Re: comparing two databasesSteve Buehler1 Oct