more complex select (for me at least)
Moderator: General Moderators
- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
more complex select (for me at least)
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
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
- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
ok
here is my select querry that i use to display a company
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 :
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 ...
...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- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
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?
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?
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
I was expecting something using this when I asked for SQL format of the table structure...
anyways....
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.
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)
)- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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
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
- Think Pink
- Forum Contributor
- Posts: 106
- Joined: Mon Aug 02, 2004 3:29 pm