mysql select where count = 0

In my db, I have a "sales" table and a "sales_item". Sometimes, something goes wrong and the sale is recorded but not the sales item's.

So I'm trying to get the salesID from my t开发者_StackOverflow社区able "sales" that haven't got any rows in the sales_item table.

Here's the mysql query I thought would work, but it doesn't:

SELECT s.*
FROM sales s NATURAL JOIN sales_item si
WHERE s.date like '" . ((isset($_GET['date'])) ? $_GET['date'] : date("Y-m-d")) . "%'
AND s.sales_id like '" . ((isset($_GET['shop'])) ? $_GET['shop'] : substr($_COOKIE['shop'], 0, 3)) ."%'
HAVING count(si.sales_item_id) = 0;

Any thoughts?

Where does the table alias v does come from? Showing the table definition would be a really good idea ;)

It has to be a left join:

SELECT *
FROM table1
LEFT JOIN table2 ON(table1.id = table2.table1_id)
WHERE table2.table1_id IS NULL

Table1 is your sales-Table and table2 is you sales_item

Having always used with Group By

GROUP BY si.sales_item_id
HAVING count(si.sales_item_id) = 0;

You don't need to join the tables, you can use something like:

[...] WHERE sales.id NOT IN (SELECT sales_id FROM sales_item)

This filters only the sales that do not have any corresponding sales_item entries.

For completeness ....

SELECT S.*
FROM SALES S 
WHERE NOT EXISTS (
    SELECT 1 
    FROM SALES_ITEM SI 
    WHERE SI.SALES_ITEM_ID = S.ID)

MySQL can have issues with IN clauses.

Assuming that each item in sales_item has an associated sales_id against it, you are probably looking for all sales that have no items.

How about using a subquery? Get all the sales_ids from the sales table where the id does not exist in the items table...

SELECT * from sales where sales_id not in (SELECT DISTINCT sales_id from sales_item)

(note : Exact syntax may be wrong, but the idea should be sound, if I understood the question correctly)

The join is restricting the rows to be displayed. My advice is to forget about the join and use instead something like this:

select * from sales where salesId not in (select salesId from sales_item)

Basically, returns sales that doesn't have any associated sales_item.

Good luck

You should probably group rows by sales item id.

SELECT s.id, count(*) as no_of_items
FROM sales s NATURAL JOIN sales_item si
WHERE s.date like '" . ((isset($_GET['date'])) ? $_GET['date'] : date("Y-m-d")) . "%'
  AND v.sales_id like '" . ((isset($_GET['shop'])) ? $_GET['shop'] : substr($_COOKIE['shop'], 0, 3)) ."%'
GROUP BY si.salesitem_id
HAVING no_of_items = 0;