Sone or later your get tired with this table construct. I recommande you to split this table into 4 tables:
Tables:
- country
ex.: id_country country_name
- company
ex.: id_company company_name
- activity
ex.: id_activity activity_name
- relation-country-company-activity ex.: id_country id_company id_activity
Example: Select countries in which the company with id 3 has activities with id 6
SELECT c.country_name FROM country AS c relation-country-company-activity AS rel WHERE rel.id_country=c.id_country AND rel.id_company=3 AND rel.id_activity=6
There are many other SELECT combinations.
Armand
---------------------------------------------------------------
Paal Eriksen wrote:
Hi i have a challenge for you. I'm struggling with this self join query. I have a table which among several contains theese fields: id, strname, iparentid
id is primary key and iparentid show who the child of id. There are 4 level deepth in this table, i.e. like this
iID--------strName-------iParentID 1----------Root--------0 2----------Africa-------1 3----------CompanyA----2 4----------Activity 1---3 5----------Activity 2---3 6----------Company B----2 7----------Europe-----1 8----------Company C----7 9----------Activity 1---8
I'm not sure of how to create this self join query, but i've started with this:
select tab1.id, tab1.strname, tab1.iparentid from tblproject tab1 inner join tblproject tab2 on tab1.iparentid=tab2.id
What i'm trying to do is to create a query who concatenate and will list the data something like this:
region, company, activity i.e.
Africa, Company A, Activity 1 Africa, Company A, Activity 2 Africa, Company B, Activity 1 Africa, Company C
So if there's Activity register it should be listed if not just list region and company.
is this possible to do?
Thanks Paal
Ny versjon av Yahoo! Messenger Nye ikoner og bakgrunner, webkamera med superkvalitet og dobbelt så morsom
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
