tags:

views:

98

answers:

7

Hi,
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 table "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?

+3  A: 

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.

Victor Stanciu
I wouldn't use "NOT IN" due to performance concerns.The left join solution provided by Tobias is a lot better.
ceteras
I think he will be using this for temporary debugging purposes, but your point is perfectly valid :)
Victor Stanciu
+4  A: 

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

Tobias P.
hi tobias, the v alias was a typo, I corrected it.Thx for your answer, it works perfectly.CU around
david parloir
A: 

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)

ZombieSheep
A: 

Having always used with Group By

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

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

Kari
A: 

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;
Michał Pękała
A: 

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.

Phil Wallach