Hello, first i would like to greet all of you because i am new on this list.
I am working on yellowpages application and am having some problems. I have different tables for storing info about different companies, their addresses & phone numbers and their field of work (i can't remember exact word in english Tables look like this: 1. Table "company": ID, name, web, email.... 2. Table "company_address": ID, ID_company, street, city, state... (one company can have multiple addresses so this table is linked to table "company" via ID_company field 3. Table "company_telephone": ID, ID_company_address, number (ID_company_address is linked to table "company_address" because on different locations companies can have multiple phone numbers) When i want to display all of the data for a company i use following query: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv FROM company LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address And this works great. Now beside this data, i want to display field of work for each company. for that, i use following tables: 4. Table "field": ID, name, l, r (l and r are used for storing data hierarchically) 5. Table "field_lookup": ID, ID_field, ID_company, rank (which is used as lookup table in which i can connect companies and their field of work. Field rank is used when you search or browse in one category so that some companies can be displayed first). OK, now i want to display list of companies with their addrersses and phone nrs. together with their field of work. I use following: SELECT company.name, city, state, street, number, company_telephone.naziv AS tel_naziv, field FROM company LEFT JOIN company_address ON company.ID = company_address.ID_company LEFT JOIN company_telephone ON company_address.ID = company_telephone.ID_company_address LEFT JOIN field ON company.ID = field_lookup.ID_company && field.ID = field_lookup.ID_field Now i expect something like that: Company1, City1, State1, Street1, Number1, Naziv1, Field1 Company2, City2, State2, Street2, Number2, Naziv2, Field2 Company3, City3, State3, Street3, Number3, Naziv3, Field3 But, it returns this: Company1, City1, State1, Street1, Number1, Naziv1, Field1 Company2, City2, State2, Street2, Number2, Naziv2, NULL Company3, City3, State3, Street3, Number3, Naziv3, NULL Company1, City1, State1, Street1, Number1, Naziv1, NULL Company2, City2, State2, Street2, Number2, Naziv2, Field2 Company3, City3, State3, Street3, Number3, Naziv3, NULL Company1, City1, State1, Street1, Number1, Naziv1, NULL Company2, City2, State2, Street2, Number2, Naziv2, NULL Company3, City3, State3, Street3, Number3, Naziv3, Field3 What seems to be problem? Is my database design good? It is important that one company can have multiple addresses, multiple phone numbers and multiple fields of work. I am also interested are there any free PHP/MySql solutions for yellow pages application. Thanks for your replies! Best regards, Marko -- Relaxen und watch das blinkenlights...
