How do I DisCOUNT that Row?
SQL has always been my weakest point as a developer. So in times like this, I usually resort to application-level solutions. This is the magic of doing pet-projects, you get to work on your weak points.
PROBLEM: The COUNT(*) function was return 1 on zero-row entries... Or was it really zero?
I was trying to do a simple COUNT on two LEFT joined tables...
------------------
items_tbl
------------------
item_id
item_name
------------------
------------------
item_entries_tbl
------------------
item_entry_id
item_id
------------------
Now, if I had the following data
------------------
items_tbl
------------------
item_id | item_name
1 | Shampoo
2 | Soap
3 | Baby Powder
and have two of these items listed on the item_entries_tbl
------------------
items_entries_tbl
------------------
item_entry_id | item_id
1 | 1
2 | 1
3 | 1
4 | 2
5 | 2
If I do the query:
SELECT A.item_id, COUNT(*) AS in_stock FROM items_tbl A LEFT JOIN item_entries_tbl B
Would give me...
------------------
SQL_RESULT
------------------
item_id | in_stock
1 | 3
2 | 2
3 | 1
Wait... There's something wrong here... I didn't have an entry for item_id 3 and yet it counted "1" in_stock. I guess the reason is that COUNT, counts for the number of rows.
When I left joined with items_tbl, it actually created a virtual row (as I like to call it) that has item_id = 3 in it. So how did I discount it?
I put in a counter-check item_id. I modified the query as follows...
SELECT A.item_id, B.item_id AS counter_check_item_id, COUNT(*) AS in_stock FROM items_tbl A LEFT JOIN item_entries_tbl B
It now gives me the result:
------------------
SQL_RESULT
------------------
item_id | counter_check_item_id | in_stock
1 | 1 | 3
2 | 2 | 2
3 | NULL | 1
At the application level, I just have to check if counter_check_item_id is NULL, the value 1 in in_stock would only be bogus and counter_check_item_id would verify if that row really exists.
In java:
//rs = java.sql.ResultSet
if ( rs.getString("counter_check_item_id") == null ){
itemEntries.setInStock(0);
}
else{
itemEntries.setInStock( rs.getInt(in_stock) );
}
Have a better solution? Post a comment! For those who does not. Hope that helps.