Page 1 of 1

more complex select (for me at least)

Posted: Tue Mar 29, 2005 12:53 am
by Think Pink
hello, I have a problem wich is a little more complex for me
please help

i have 3 tables

subdomain
id_subdomain
cod_domain
subdomain_name

companyes
id_company
company_name
main_activity

secondary_activity
id_sec_activity
code_subdomain
companyId


the ideea is the following
i have a page domains.php where are listed all the domains. when i click on a domain
I go to subdomains.php. I get the domain id from the address bar and interogate the $db. I list then all the subdomains that have the same foreign key with the domain id i choosed before.
until here is simpple.
Further, whe i choose a subdomain i want to select all the companyes that have that subdomain as their main activity or secondary activity. The problem is only the companyes that have the selected subdomain as the main activity are listed.
Can anyone tell me how to display all the companyes that have the subdomain?

i hope i made my self clear, if not, i can provide more explanations.
thanks in advance

Posted: Tue Mar 29, 2005 5:30 am
by feyd
okay.. I have no idea how your tables link together.. Please post a real table structure (SQL format), and a description of which fields link to where please.

Posted: Tue Mar 29, 2005 8:28 am
by Think Pink
ok
here is my select querry that i use to display a company

Code: Select all

$sql = "SELECT * FROM company, subdomains WHERE (company.main_activity = subdomain.id_subdomain)";
...
for ...
...
and this would output
Id company | Company Name | Main Activity
1 | company_1 | programming
2 | company_2 | computers
3 | company_3 | programming

the structure of company table is
id_company | company_name | main_activity
1 | company_1 | 1
2 | company_2 | 2
3 | company_3 | 1

the strtucture of subdomain is
id_subdomain | code_domain | subdomain_name
1 | 1 | programming
2 | 1 | computers
3 | 17 | discoteque


the structure of domain is :
id_domain | domain_name
1 | IT&C
2 | some_other_domain
...............................
17 | entertainment

the structure of secondary_activity is :
id_secondary | code_subdomain | companyId
1 | 3 | 3

this means that
company_3 has the main_domain programming and as a secondary_activity discoteque



the domain.id_domain links with subdomain.code_domain
subdomain.id_subdomain links with company.main_activity
subdomain.id_subdomain links with secondary_activity.code_subdomain
secondary_activity.companyId links with company.id_company


again, when i click a subdomain i want to see all the companyes that have that subdomain in their activity, it doesn't matter if that subdomain is their main or secondary activity.

the sql (i think) should look something like this :

Code: Select all

$sql = "SELECT * FROM company, subdomain, secondary_activity WHERE (company.code_subdomain = '".$id_subdomain."') OR (secondary_activity.code_subdomain = '".$id_subdomain."')";
......
for ....
end for

Posted: Tue Mar 29, 2005 8:36 am
by CoderGoblin
And the result of your query is.... :?:

(You may also need to add company_id=x)

Posted: Tue Mar 29, 2005 9:09 am
by Think Pink
ok, you don't have to take everything I say as good, because i don't know much about sql, so maybe this will help you help me.

let's say i have in my $db 4 companyes
company_1 has main_activity programming and no second activity.
company_2 has main_activity publisher and as second activity reading
company_3 has main activity climbing_trees and secondary activity programming
company_4 has main activity climbing_mountains and secondary activities programming, reading, watching_TV

if i click on programming subdomain how do i display
company_1
company_3
company_4

because these companyes hav as subdomain (main or secondary, it doesn't matter) programming

if i click on reading subdomain i would display only 2 companyes
company_2
company_4

but how do i do that?

Posted: Tue Mar 29, 2005 9:15 am
by CoderGoblin
Ignore the part I mentioned about limiting the company. Have you tried your query? What is the result. As far as I can see you are well on the way...

Posted: Tue Mar 29, 2005 9:28 am
by feyd
I was expecting something using this when I asked for SQL format of the table structure...


anyways....

Code: Select all

SELECT
	DISTINCT
	d.*
FROM
	`company` d
INNER JOIN
	`subdomain` s
	ON
		s.id_subdomain = '123'
LEFT JOIN
	`company` c
	ON
		c.main_activity = s.id_subdomain
		AND
		c.id_company = d.id_company
LEFT JOIN
	`secondary_activity` a
	ON
		a.code_subdomain = s.id_subdomain
		AND
		a.companyId = d.id_company
WHERE
	(
		(a.code_subdomain IS NULL AND c.main_activity IS NOT NULL)
		OR
		(a.code_subdomain IS NOT NULL AND c.main_activity IS NULL)
	)
This would be a LOT easier if you restructured such that you only had 1 table for activities. They query would likely perform many times faster too.

Posted: Tue Mar 29, 2005 9:44 am
by Think Pink
This would be a LOT easier if you restructured such that you only had 1 table for activities....
ok, but how do i set only one main activity?

Posted: Tue Mar 29, 2005 10:09 am
by feyd
a flag field.

The table structure would be such that you have a activity ID, and activity name, basically. Then a seperate table links to two sets of tables together.. allowing a many-to-many relationship.

This may be of interest: Chapter 2: Relational Data Architecture

Posted: Thu Mar 31, 2005 1:19 pm
by Think Pink
ok feyd solved it in a more simple way, creating one table for activities and one for company details.

i'll try though your advice too.
thx.