----- Original Message ----- From: "Marko Knezevic" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, August 31, 2004 12:53 PM Subject: Multiple JOINs
> 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. > Your design seems fine, although you haven't explained how the field and field_lookup tables join to the others so I am making some assumptions. I think your problem is that some of your Left Joins should be Inner Joins. Are you familiar with the difference? Remember, a left join will pick up "orphan rows" - rows that don't have an equal, non-null key in the other table - and add them to the result. I think that's what is happening here. In fact, looking at your query and data, I'm not sure why any of your joins are Left Joins. It seems to me that each of your joins should only take place if a given pair of tables has equal, non-null keys. I don't see why you would want *any* "orphan rows" in your result. At the very least, I would make the join involving the Field table an inner join and see what happens. The other thing that strikes me as a possible problem is the '&&' operator in the last join. I mostly use DB2 but it doesn't have this operator so I'm not completely sure what '&&' will do in a MySQL join. (I am familiar with the '&&' operator in programming languages, like Java, I've just never seen it used in joins before.) You might get a better result if you didn't use the '&&' operator and added another join for the Field_Lookup table. Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
