Join three (3) MySQL tables

unfortunately i'm not that good as SQL and i'm trying to get a join between three tables done.

here's a rough simplified table structure:

links: id, url, description
categories: id, name, path
link_cat: link_id, cat_id

The select statement I'm aiming for should have

links.id, link.url, link.description, categories.name, categories.path

Where links and categories are matched via the link_cat table. I think that shouldn't be too hard as long as there's only one category for each link. This is what I'm assuming. If not it would be good to have another way that pulls multiple categories comma separated into the categories.name field.

I hope this is all understandable and doesn't sound too silly.

Answers


SELECT links.id, links.url, links.description, categories.name, categories.path
FROM links
INNER JOIN link_cat ON links.id = link_cat.links_id
INNER JOIN categories ON categories.id = link_cat.category_id

# Add each field you want to the select list
SELECT links.id, link.url, link.description, categories.name, categories.path

# Add the "links" table to the list of tables to select from
FROM links

# Add the "link_cat" table, specify "link_id" as the common field
JOIN link_cat USING (link_id)

# Add the "categories" table specifying the "cat_id" as the common field
JOIN categories USING (cat_id)

http://sqlfiddle.com/#!9/6a81e/1

SELECT 
  l.id, 
  l.url, 
  l.description,
  c.name,
  c.path
FROM links l
INNER JOIN link_cat lc
ON lc.link_id = l.id
INNER JOIN categories c
ON lc.cat_id = c.id 

SELECT links.id, links.url, links.description, categories.name, categories.path
FROM links
INNER JOIN link_cat     ON link_cat.link_id = links.id
INNER JOIN categories   ON link_cat.cat_id  = categories.id

SELECT link.id, link.url, link.description, categories.name, categories.path
FROM link, categories, link_cat
WHERE
    link.id = link_cat.link_id
    AND categories.id = link_cat.category_id

Need Your Help

Magento display message on page

validation magento email post input

I start to give my luck on magento for weeks now. Challenges since then. I am trying to display message above my input box but to no avail. Below are my phtml and controller.

Can't click textbox after validation fails

c# wpf validation binding

I'm working on trying to implement validation in WPF, and running into an issue where I can't click on or change the value of a textbox after validation fails.