以下 sql 查询中的错误处理 [英] Error handling in the following sql query

问题描述

摆弄表格这里

我将以下 sql 与小提琴中的表一起使用，以检查用户是否已达到借用限制.这里的问题是，如果提供了无效的物品编号，则返回 NULL，如果用户没有借过任何物品，则返回 NULL.这样，我无法判断是否提供了无效的项目编号，或者用户是否实际上没有借过任何书籍.有什么好方法可以检查是否提供了无效的物品编号或会员实际上没有在该类别下借过任何东西?

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

推荐答案

我能得到的最远的一个查询是(必须取出 php 和国家"变量才能使小提琴工作):

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

您可能需要对有效的 memId 进行预备查询检查:

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

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

然后在这里测试:

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

这篇关于以下 sql 查询中的错误处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！