How to Exclude Rows That Don't Join with Another Table

How to exclude rows that don't join with another table?

SELECT <select_list> 
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL

Full image of join

From aticle : http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

mySQL Join Tables but exclude ALL rows with a certain ID/value based on matching criteria from another column (criteria contained in only some rows)

SELECT s.SampleNo, s.SampleType, s.BatchNo, s.Status, b.Info FROM sample_table s 
INNER JOIN batch_table b on s.BatchNo = b.BatchNo
WHERE s.BatchNo NOT IN (
           SELECT DISTINCT(BatchNo) FROM sample_table WHERE Status != 'pass' AND 
           SampleType IN ('Batch_QC_2', 'Batch_QC_1')
          );

exclude records that exist in another table

You can use NOT EXISTS :

SELECT ....
FROM   .... 
WHERE  ..... // Replace the dots with Your Query
  AND NOT EXISTS(SELECT 1 FROM blocked
                 WHERE emails.memberID = blocked.memberID)
  AND NOT EXISTS(SELECT 1 FROM markedAsRead
                 WHERE emails.emailID = markedAsRead.emailID)

You could also lookup for LEFT JOINS or NOT IN to exclude records that doesn't exists in a particular table.

EDIT: Usually EXISTS() and LEFTJOIN have similar performaces, sometime it can even perform better than a join.

LEFT JOIN sulotion:

SELECT ...
FROM ...
LEFT JOIN blocked 
 ON(WHERE emails.memberID = blocked.memberID)
LEFT JOIN markedAsRead
 ON(emails.emailID = markedAsRead.emailID)
WHERE ...
 AND blocked.memberID IS NULL
 AND markedAsRead.emailID IS NULL

Exclude rows with no associated row in a joined table

The SQL query you're looking for is this:

SELECT foo.* FROM foo LEFT JOIN bar ON bar.foo_id = foo.id WHERE bar.foo_id IS NULL;

LEFT JOIN, as opposed to INNER JOIN, includes all rows from the 'left' table (i.e. the table specified in FROM tblname), even if they have no associated row in the 'right' table (the table specified in JOIN tblname). This means that rows that have no associated row in the right table will have NULL values instead:

foo.id | foo.baz | bar.id | bar.foo_id | bar.zab
-------+---------+--------+------------+--------
     1 |      23 |   NULL |       NULL |    NULL
     2 |      56 |      7 |          2 |      s1

So, filter for those rows that have a NULL in the right table's primary key (which can't be null in any other case, whereas other columns from the right table could be), and you get foo rows that have no associated bar.

In SQLAlchemy, that becomes this:

q = db.session.query(Foo).join(Bar, isouter=True).filter(Bar.id == None)

The isouter=True flag to join is how you do a LEFT JOIN with SQLAlchemy.

Exclude records if right table matches

SELECT
  A.*
FROM
  A
LEFT JOIN
  B
  ON A.id = B.A
  AND B.cond = 'X'
WHERE
  B.A IS NULL

This query joins the tables based on the conditions you specified, and then only selects the rows where there's no match in table B.

How to exclude rows when using a LEFT JOIN (MySQL)

Taking your requirements and translating them literally to SQL, I get this:

SELECT users.id,
       COUNT(posts.id) as posts_count,
       COUNT(approved_posts.id) as approved_posts_count
FROM users
LEFT JOIN posts ON posts.user_id = users.id
LEFT JOIN posts approved_posts
  ON approved_posts.status = 'approved'
  AND approved_posts.user_id = users.id
WHERE users.status = "active"
GROUP BY users.id
HAVING (posts_count = 0 OR approved_posts_count = 0);

For your test data above, this returns:

4|1|0
5|0|0

i.e. users with ids 4 and 5, the first of which has 1 post but no approved posts and the second of which has no posts.

However, it seems to me that this can be simplified since any user that has no approved posts will also have no posts, so the union of conditions is unnecessary.

In that case, the SQL is simply:

SELECT users.id,
       COUNT(approved_posts.id) as approved_posts_count
FROM users
LEFT JOIN posts approved_posts
  ON approved_posts.status = 'approved'
  AND approved_posts.user_id = users.id
WHERE users.status = "active"
GROUP BY users.id
HAVING approved_posts_count = 0;

This also returns the same two users. Am I missing something?

Remove clients who don't have 2 rows by their name in SQL

you need something like this :

select * from yourtable 
where not exists (select 1 from yourtable where order_number >1)

or:

select client 
from tablename
group by client
having count(*) > 1

---

Related Topics