Error handling in the following sql query

Question

Fiddle with tables here

I'm using the following sql with the tables in the fiddle to check if a user has reached the borrowing limit. The problem here is, If an invalid item number were supplied it returns NULL, if a user has not borrowed any items, it returns NULL. This way, I cannot tell if a invalid item number were supplied or if a user actually has not borrowed any books. What would be a good way to check if a invalid item number was supplied or a member actually has not borrowed anything under that category?

set @mId = 3 //Has not borrowed anything till now.
set @id = 21; //This item does not appear in the collection_db table and is therefore invalid.
set @country = 'US';

SELECT col1.id, col1.holder, col2.borrowMax maxLimit, count(lend.borrowedId) as `count`
FROM collection_db col1
  INNER JOIN collection_db col2
  ON col1.holder = col2.id
    INNER JOIN lendings lend
    ON col1.holder = lend.holder and col1.country = lend.country
WHERE col1.id = @id and col1.country = @country
AND col2.category = 10
AND lend.memId = @mId and lend.country = @country

My tables you mean? Collection_db hold items that a user can borrow. Lending holds what has been lent. Category 10 indicates its not an item, but a label. Anything else u wanted to know? — jmenezes
– jmenezes, Commented May 24, 2013 at 8:52
That's how I understood your question. If there were other things you wanted to know, pls ask. — jmenezes
– jmenezes, Commented May 24, 2013 at 9:06

Maxwell Donathan · Accepted Answer · 2013-05-24 13:52:47Z

1

The furthest I could get with the one query is (had to take out php and "country" vars for fiddle to work):

SELECT col1.id, col1.holder, col2.borrowMax maxLimit, count(lend.borrowedId) as `count`
,case when valid1.id is not null then 'true' else 'false' end as validId
FROM collection_db col1
  INNER JOIN collection_db col2
  ON col1.holder = col2.id
    INNER JOIN lendings lend
    ON col1.holder = lend.holder,(
        Select Distinct a.id From collection_db a
        Where a.id = 4) valid1
WHERE col1.id = 4
AND col2.category = 10
AND lend.memId = 1

You may have to do a preparatory query checking for a valid memId:

$theQuery = "SELECT DISTINCT memId FROM lendings WHERE memId = 1"

Then test it here:

if (mysql_num_rows(mysql_query($theQuery)) <= 0) { /* No memId exists */ }
else { /* Do big query here */ }

answered May 24, 2013 at 13:52

Maxwell Donathan

1,53710 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Adder · Accepted Answer · 2013-05-24 08:29:27Z

0

You can use a tableA LEFT JOIN tableB, which will return results for the tableA even if tableB has no matches and will return NULL values for those in tableB.

Unfortunately, I can't quite figure out where you need LEFT JOINS, but probably you want them in both places.

You also might have to reorder the tables if it is the first table that should be on the right side of a LEFT JOIN. You could use a RIGHT JOIN but it is less readable to me.

answered May 24, 2013 at 8:29

Adder

5,8982 gold badges35 silver badges60 bronze badges

Comments

Nightmare · Accepted Answer · 2013-05-24 08:30:16Z

0

maybe you should try "left join" if col1 do not have too much data,or do the query step by step

answered May 24, 2013 at 8:30

Nightmare

2511 silver badge5 bronze badges

Collectives™ on Stack Overflow

Error handling in the following sql query

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related