SQL Where Joined Set Must Contain All Values But May Contain More

SQL where joined set must contain all values but may contain more

Group by offer.id, not by sports.name (or sports.id):

SELECT o.*
FROM   sports        s
JOIN   offers_sports os ON os.sport_id = s.id
JOIN   offers        o  ON os.offer_id = o.id
WHERE  s.name IN ('Bodyboarding', 'Surfing') 
GROUP  BY o.id  -- !!
HAVING count(*) = 2;

Assuming the typical implementation:

• offer.id and sports.id are defined as primary key.
• sports.name is defined unique.
• (sport_id, offer_id) in offers_sports is defined unique (or PK).

You don't need DISTINCT in the count. And count(*) is even a bit cheaper, yet.

Related answer with an arsenal of possible techniques:

• How to filter SQL results in a has-many-through relation

---

Added by @max (the OP) - this is the above query rolled into ActiveRecord:

class Offer < ActiveRecord::Base
  has_and_belongs_to_many :sports
  def self.includes_sports(*sport_names)
    joins(:sports)
      .where(sports: { name: sport_names })
      .group('offers.id')
      .having("count(*) = ?", sport_names.size)
  end
end

SQL - child must contain all specified values

I'm not sure about "more correct", but a simple JOIN with GROUP BY/HAVING will do it without a subquery;

SELECT test_parent.parent_id, test_parent.title 
FROM test_parent
JOIN test_child ON test_child.parent_id=test_parent.parent_id
 AND test_child.property IN ('A','B')
GROUP BY test_parent.parent_id, test_parent.title
HAVING COUNT(DISTINCT test_child.property)=2

An SQLfiddle to test with.

It will basically join the parent with any child that has a property equal to 'A' or 'B', group by the parent row and count the distinct values of property on the child. If it's equal to 2 ('A' and 'B' being the two possible values), return the parent.

INNER JOIN where **every** row must match the WHERE clause?

SELECT  *
FROM    a
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    b
        WHERE   b.a_id = a.a_id
                AND (b.value <= 2 OR b.value IS NULL)
        )

How can a LEFT OUTER JOIN return more records than exist in the left table?

The LEFT OUTER JOIN will return all records from the LEFT table joined with the RIGHT table where possible.

If there are matches though, it will still return all rows that match, therefore, one row in LEFT that matches two rows in RIGHT will return as two ROWS, just like an INNER JOIN.

EDIT:
In response to your edit, I've just had a further look at your query and it looks like you are only returning data from the LEFT table. Therefore, if you only want data from the LEFT table, and you only want one row returned for each row in the LEFT table, then you have no need to perform a JOIN at all and can just do a SELECT directly from the LEFT table.

Left Join With Where Clause

The where clause is filtering away rows where the left join doesn't succeed. Move it to the join:

SELECT  `settings`.*, `character_settings`.`value`
FROM    `settings`
LEFT JOIN 
       `character_settings` 
ON     `character_settings`.`setting_id` = `settings`.`id`
        AND `character_settings`.`character_id` = '1'  

SQL JOIN - WHERE clause vs. ON clause

They are not the same thing.

Consider these queries:

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID
WHERE Orders.ID = 12345

and

SELECT *
FROM Orders
LEFT JOIN OrderLines ON OrderLines.OrderID=Orders.ID 
    AND Orders.ID = 12345

The first will return an order and its lines, if any, for order number 12345. The second will return all orders, but only order 12345 will have any lines associated with it.

With an INNER JOIN, the clauses are effectively equivalent. However, just because they are functionally the same, in that they produce the same results, does not mean the two kinds of clauses have the same semantic meaning.

How to do join on multiple criteria, returning all combinations of both criteria

select one.*, two.meal
from table1 as one
left join table2 as two
on (one.weddingtable = two.weddingtable and one.tableseat = two.tableseat)

Query problem-getting wrong result when a condition or a set of data included

At the very least, you should avoid the way you update the table. You should be carefull with joins on update. If you happen to have more than one value for the same row, the result is not deterministic. Better use this form:

update table1
set table1.grade = (SELECT TOP 1 table3.grade FROM table3
                    WHERE table3.value < table1.max
                    ORDER BY table3.value DESC)

---

Related Topics