single sql query for multiple tables

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.

You are only going to get single rows (ie. 1 row per keyword per each domain) unless you want to concat the keywords into a single column. So the means youll have to group them together into nested structures as you read the results (in whatever scripting language youre using). The group by should ensure you only get one instance of the keyword per domain. By using a JOIN instead of a left JOIN you also ensure you dont pull domains/keywords that dont have combinations together.

SELECT d.domain_name, k.keyword
FROM pages p, keywords k, domains d
WHERE p.id_domain  = d.id 
AND p.id_keyword = k.id
GROUP BY d.domain_name, k.keyword
ORDER BY d.domain_name 

Try the following. You can select the columns you want, for now I'm pulling all.

SELECT * FROM pages
 JOIN keywords ON pages.id_keyword = keywords.id
 JOIN domains ON pages.id_domain = domains.id;

MySQL may not be the best way to group the results based on the amount of data you want returned. But you can easy do this with PHP though as you are looping of the results.

select d.domain_name, k.keyword 
from pages as p 
JOIN domains as d ON d.id=p.id_domain 
JOIN keywords as k ON k.id=p.id_keyword 
group by p.id_domain, p.id_keyword