Select All Records Don't Meet Certain Conditions in a Joined Table

Select all records don't meet certain conditions in a joined table

SELECT *
FROM posts p
WHERE NOT EXISTS(
    SELECT 1
    FROM comments c
    WHERE c.comment_date >= 'deadline'
    AND p.post_id = c.post_id
)

How to select rows with no matching entry in another table?

Here's a simple query:

SELECT t1.ID
FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL

The key points are:

1. LEFT JOIN is used; this will return ALL rows from Table1, regardless of whether or not there is a matching row in Table2.

2. The WHERE t2.ID IS NULL clause; this will restrict the results returned to only those rows where the ID returned from Table2 is null - in other words there is NO record in Table2 for that particular ID from Table1. Table2.ID will be returned as NULL for all records from Table1 where the ID is not matched in Table2.

How to select all records from one table that do not exist in another table?

SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL

Q: What is happening here?

A: Conceptually, we select all rows from table1 and for each row we attempt to find a row in table2 with the same value for the name column. If there is no such row, we just leave the table2 portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name column (the one we are sure that exists, from table1).

While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL

How can I join two tables, keeping rows that do not meet the JOIN condition?

you should be using LEFT JOIN

SELECT  a.staff,    COUNT(b.assign) as count 
FROM    staffinfo a
        LEFT JOIN ticket b
            ON b.assign = a.staff
GROUP   BY a.staff

• SQLFiddle Demo

To fully gain knowledge about joins, kindly visit the link below:

• Visual Representation of SQL Joins

How do I find records that are not joined?

select * from a where id not in (select a_id from b)

Or like some other people on this thread says:

select a.* from a
left outer join b on a.id = b.a_id
where b.a_id is null

SQL: Select records where ALL joined records satisfy some condition

Assuming no need for correlation, use:

SELECT a.*
  FROM A a
 WHERE EXISTS(SELECT NULL
                FROM B b
              HAVING MIN(b.some_val) > a.val)

If you do need correlation:

SELECT a.*
  FROM A a
 WHERE EXISTS(SELECT NULL
                FROM B b
               WHERE b.id = a.id
              HAVING MIN(b.some_val) > a.val)

Explanation

The EXISTS evaluates on a boolean, based on the first match - this makes it faster than say using IN, and -- unlike using a JOIN -- will not duplicate rows. The SELECT portion doesn't matter - you can change it to EXISTS SELECT 1/0 ... and the query will still work though there's an obvious division by zero error.

The subquery within the EXISTS uses the aggregate function MIN to get the smallest B.some_val - if that value is larger than the a.val value, the a.val is smaller than all of the b values. The only need for a WHERE clause is for correlation - aggregate functions can only be used in the HAVING clause.

Obtain all the records in table A, if they meet the conditions with table B

There are some problems in your query/logic:

• select max(id), name, ... group by uid will not give you the row with the maximum id. It will give you the maximum id, and the values of any row that is in that group. If there is only one row per group (e.g. if uid is unique/the primary key), that might be the one you are looking for, otherwise it is not determined (and will fail for MySQL 5.7), see MySQL Handling of GROUP BY and any question on stackoverflow about an errormessage with sql_mode=only_full_group_by.

• left join ... ON bonus_id = id where rb1.player_id IS NULL will be false if there is any player that has redeemed this bonusid. If you included the playerid in the on-condition, it would be true if the player would not have redeemed all different ids for a given uid (which is probably impossible).

• something similar happens since you join via rb1.bonus_id = br1.id and apply your condition to this id (but not uid): if there is some old entry with a bigger redeem_count, it evaluates to true even if there is a latest id with a lower redeem_count (that won't be part of the group by, since you filtered it out).

• instead, you probably would need to apply your filter after left join, e.g using group by ... having ... or select ... from (select ... group by ...) where ...

With this said, I won't fix your query (although it may be salvageable), but write you a new one with a new structure.

Breaking it into steps, first, get a list of all active bonuses:

select * from bonus_records br
where not exists 
  (select 1 from bonus_records br1
   where br1.uid = br.uid and br1.id > br.id);

Next step is to check how often a specific uid has been redeemed by a specific player (the uid-information is obtained by checking the bonus_records-table):

select br.uid, count(*) 
from redeemed_bonuses rb
join bonus_records br on br.id = rb.bonus_id
where rb.player_id = 1
   and not (rb.completed = 0 and rb.canceled = 0) 
group by br.uid;

The condition not (rb.completed = 0 and rb.canceled = 0) is adepted to fit the requirements according to the comments.

Now join those two and apply your conditions about the actual count being lower than redeem_count:

select pb.*, rd.actual_count from 
  (select * from bonus_records br
   where not exists 
     (select 1 from bonus_records br1
      where br1.uid = br.uid and br1.id > br.id)
  ) pb -- active potential bonuses
left join  
  (select br.uid, count(*) as actual_count 
   from redeemed_bonuses rb
   join bonus_records br on br.id = rb.bonus_id
   where rb.player_id = 1
      and not (rb.completed = 0 and rb.canceled = 0) 
   group by br.uid
  ) rd -- redeemed bonuses by that user
on pb.uid = rd.uid 
where rd.actual_count is null            -- uid never redeemed (left join empty)
   or rd.actual_count < pb.redeem_count  -- still some remaining
   or pb.redeem_count = 0                -- unlimited bonus

Selecting all rows from one table where condition in joined table

Try this

  SELECT  
     pro.productname, 
     pr.productionreportid, 
     IFNULL(pr.qty, 0) qty
  FROM 
    Products pro  
  LEFT JOIN ProductionReport pr  
    ON pro.productid = pr.productid 
    AND DATE(pr.date) = '2013-04-08'

Basically move the date condition from WHERE clause to the JOIN clause

Find records from one table which don't exist in another

There's several different ways of doing this, with varying efficiency, depending on how good your query optimiser is, and the relative size of your two tables:

This is the shortest statement, and may be quickest if your phone book is very short:

SELECT  *
FROM    Call
WHERE   phone_number NOT IN (SELECT phone_number FROM Phone_book)

alternatively (thanks to Alterlife)

SELECT *
FROM   Call
WHERE  NOT EXISTS
  (SELECT *
   FROM   Phone_book
   WHERE  Phone_book.phone_number = Call.phone_number)

or (thanks to WOPR)

SELECT * 
FROM   Call
LEFT OUTER JOIN Phone_Book
  ON (Call.phone_number = Phone_book.phone_number)
  WHERE Phone_book.phone_number IS NULL

(ignoring that, as others have said, it's normally best to select just the columns you want, not '*')

---

Related Topics