> -----Ursprüngliche Nachricht----- > Von: Renito 73 [mailto:[EMAIL PROTECTED] > Gesendet: Samstag, 30. September 2006 04:20 > An: mysql@lists.mysql.com > 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é -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
