CODEWITHSUNDEEP
mysqlselect
kaseOga
kaseOga

Reputation: 781

SELECT in multiple tables

I have this 3 tables:

POST [id_post, url]
CAT [id_cat, name]
POST_CAT [id_post, id_cat]

I have 10-15 categories, and between 100-200 urls to each category, but the problem is the urls can have more than one category, then... i want show ALL categories with 1 url, but that url can't be showed in the other categories

How can i do?

I have this SQL but obviously don't do what i want:

SELECT c.nombre, p.url 
FROM post_cat pc, posts p, categorias c
WHERE pc.id_post = p.id_post 
AND c.id = pc.id_cat 
GROUP BY pc.id_cat

Thank you and sorry for my bad english!!!

--------–--------–--------–--------–--------–

Data (Example): 

CAT 1 - URL1, URL2
CAT 2 - URL1, URL2
CAT 3 - URL2, URL3
CAT 4 - URL1, URL2

What this show:

CAT1 - URL1
CAT2 - URL2
CAT3 - URL3

CAT4 - URL1 (cause URL1 and URL2 is shown in other categories... show 1 of them independent if is repeated or not)

Upvotes: 0

Views: 89

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If I understand correctly, you want a unique url for each category, where that url has no other categories. Unfortunately, there is no guaranteed solution to this in SQL. But you can come close.

The first thing is to choose an arbitrary category for each url:

select p.url, min(pc.id_cat) as id_cat
FROM post_cat pc join
     posts p
     on pc.id_post = p.id_post 
GROUP BY p.url
HAVING count(*) = 1;

The having clause guarantees that there is just one category for the url.

Next, you want to choose one of these for each category:

select id_cat, min(url) as url
from (select p.url, min(pc.id_cat) as id_cat
      FROM post_cat pc join
           posts p
           on pc.id_post = p.id_post 
      GROUP BY p.url
      HAVING count(*) = 1
group by id_cat;

If you want additional category information (such as the name), then join in the categories table. ) p

Upvotes: 1

digitai
digitai

Reputation: 1842

By using Joins you can relate as many tables as you need. Joins are the heart of relational datebases.

Select c.nombre, p.url from post_cat pc join posts p on c.id=pc.id_cat 
join categorias c on pc.id_cat=c.id_cat

Also consider the use of Left Join, Right Join, Union.

Upvotes: 0

Phil
Phil

Reputation: 824

Try GROUP_CONCAT():

SELECT GROUP_CONCAT(c.nombre), p.url 
FROM post_cat pc, posts p, categorias c
WHERE pc.id_post = p.id_post 
AND c.id = pc.id_cat 
GROUP BY pc.id_cat

This creates one text field containing all values of c.nombre as grouped by your GROUP BY clause.

See here for the full documentation about this:

Upvotes: 0

Related Questions