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.