Mysql – Selecting row by field and exact list of join id

join;MySQLselect

I have 2 tables. For simplicity, first table Parent contains only id and name. Other table Child contains id and parent_id.

There are going to be many parent rows with the same name. They will have different childrens, so I want to be able to select a parent row only if it has childs from the list I provide.

Let's say I want a Parent with name 'test' only if it has Childs with id (223, 224, 225).

So far I achieved this with following query:

SELECT p.*, GROUP_CONCAT(c.id) as childs
FROM `parent` p
    INNER JOIN `child` c ON p.id = c.parent_id
WHERE p.name = 'test'
GROUP BY p.name
HAVING childs = '223,224,225'
ORDER BY c.id

However I think it's not a good solution if it uses id concatenation

Best Answer

You can apply conditional aggregation

HAVING sum(case when c.id in (223,224,225) then 1 else -1 end) = 3

This assumes that those ids are unique.

Related Solutions

Mysql – Nested query not recognize external table

The reason for the error is explained in MySQL documentation in part E.4. Restrictions on Subqueries as:

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized in whole (evaluated to produce a result set) during query execution, so they cannot be evaluated per row of the outer query.

Changing query a bit like the following seems to solve your problem:

SELECT t.CodC, c.NameC, t.Amount, t.Year, t.Week, 
    IF(t.Week!=@week OR t.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank, 
    @week:=t.Week, @year:=t.Year 
FROM
    (SELECT CodC, Sum(Imports) As Amount, Year, Week
        FROM Weekly
        WHERE Year=2014
        GROUP BY CodC, Year, Week
        ORDER BY Year DESC, Week DESC, Amount DESC) t
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t2 ON 1
    LEFT JOIN Companies c ON c.CodC=t.CodC

It basically adds an artificial rank column by checking year and week values on the ordered rows.

Edit

Previous query is given to order by weekly sum because of misunderstanding. Following query will give what you want

SELECT t2.*,
    IF(t2.Week!=@week OR t2.Year!=@year, @rank:=1, @rank:=@rank+1) AS rank, 
    @week:=t2.Week, @year:=t2.Year 
FROM
    (SELECT t1.*, SUM(w2.Imports) AS cnt
    FROM
        (SELECT w1.CodC, c.NameC, w1.Week, w1.Year
        FROM Weekly w1
            LEFT JOIN Companies c on c.CodC=w1.CodC
            ORDER BY w1.Year ASC, w1.Week ASC) t1
            LEFT JOIN Weekly w2 ON t1.CodC=w2.CodC AND w2.Year=t1.Year AND w2.Week<=t1.Week
            GROUP BY t1.CodC, t1.Week, t1.Year
            ORDER BY t1.Year DESC, t1.Week DESC, cnt DESC
            )t2
    JOIN (SELECT @rank:=0, @week:=-1, @year:=-1) t3 ON 1

Fiddle

Mysql – SQL Join Field and Content

According with your last edit:

SELECT
    F.*,
    C.*
FROM db1.Fields AS F
JOIN db1.Content AS C ON (C.ID = F.ID AND C.ForUser = F.ForUser 
                          AND C.ForCategory = F.ForCategory)
WHERE `the field you want to filter`=`the value you want to search`;

You can filter with Any field in Fields or Content.

Best Answer

Related Solutions

Mysql – Nested query not recognize external table

Edit

Mysql – SQL Join Field and Content

Related Question