Database related Q

[Cpt. Zeep]

Although this is not strictly PHP related question, i presume lots of you
are good in MySql so maybe you could help me. I am making sort of
yellowpages application and have following issue:

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_addres s": 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_teleph one": ID, ID_company_addr ess, number
(ID_company_add ress is linked to table "company_addres s" 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_telepho ne.naziv AS tel_naziv
FROM company
LEFT JOIN company_address ON company.ID = company_address .ID_company
LEFT JOIN company_telepho ne ON company_address .ID =
company_telepho ne.ID_company_a ddress

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_look up": 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_telepho ne.naziv AS tel_naziv, field
FROM company
LEFT JOIN company_address ON company.ID = company_address .ID_company
LEFT JOIN company_telepho ne ON company_address .ID =
company_telepho ne.ID_company_a ddress
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.. .

[Geoff Berrow]

Re: Database related Q

I noticed that Message-ID: <ch0127$61v$1@b agan.srce.hr> from Cpt. Zeep
contained the following:
[color=blue]
>SELECT company.name, city, state, street, number,
>company_teleph one.naziv AS tel_naziv, field
>FROM company
>LEFT JOIN company_address ON company.ID = company_address .ID_company
>LEFT JOIN company_telepho ne ON company_address .ID =
>company_teleph one.ID_company_ address
>LEFT JOIN field ON company.ID = field_lookup.ID _company &&
>field.ID = field_lookup.ID _field[/color]

Well if you get the bit with the joining table working first you can
than just add it on to the other bit.

I prefer to use the where clause, I think it's simpler to understand

SELECT f1,f2 FROM t1,t2,t3 where t1.t1_id=t2.t1_ id and t2.t3_id=t3.t3_ id

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/