List:General Discussion« Previous MessageNext Message »
From:Renito 73 Date:September 30 2006 2:20am
Subject:Joining *3* tables
View as plain text  
Hello list

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.

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?

Thanks for your comments

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