sql query for multiple tables via a linking table

[scarhand]

i need some help with this sql query

 

heres my mysql table structure:

 

DOMAINS

- id

- domain_name

 

KEYWORDS

- id

- keyword

- keyword_slug

 

PAGES

- id

- id_domain

- id_keyword

 

what i need is my sql query to give me results in this format:

 

- domain_name

-- keyword in domain linked via the "pages" table

-- keyword in domain linked via the "pages" table

-- keyword in domain linked via the "pages" table

- domain_name

-- keyword in domain linked via the "pages" table

-- keyword in domain linked via the "pages" table

-- keyword in domain linked via the "pages" table

 

so i was thinking something like this? not sure how to get the grouping, confusing me a little:

 

$sql = mysql_query("select d.domain_name, k.keyword from domains as d, keywords as k, pages as p where d.id=p.id_domain and k.id=p.id_keyword group by p.id_domain");

 

isn't exactly working....any help would be much appreciated.

 

[Sock Puppet]

This should do what you want:

 

select d.domain_name, k.keyword
from domains as d, keywords as k, pages as p
where d.id=p.id_domain and k.id=p.id_keyword
order by p.id_domain

 

The only difference from yours is order by instead of group by. It won't be exactly the format you described, but it should be easy to write some php that works with these results. They'll look like:

 

- domain name, keyword in domain

- domain name, keyword in domain

- domain name, keyword in domain

- domain name, keyword in domain

- domain name, keyword in domain

 

Because of the order by clause though, all the records for a given domain id will be grouped together in your result set, which is (mostly) what you wanted.