I have this in info in a table where sometime the pd_id
is NULL
doc_id doc_title doc_order pg_id
14 a.zip 1 NULL
15 b.zip 2 NULL
12 c.zip 1 16
13 d.zip 2 16
3 f.doc 3 16
4 g.doc 4 16
When I want to query the item with the pg_id 16, I do this,
SELECT *
FROM root_documents
WHERE root_documents.pg_id = '16'
While when I want query the item without any pd_id, I do this,
SELECT *
FROM root_documents
WHERE root开发者_C百科_documents.pg_id IS NULL
I find two queries are repetitive, so I try to this,
SELECT *
FROM root_documents
WHERE (root_documents.pg_id = ? OR root_documents.pg_id IS NULL)
So when I want to get this result only when I query the item with pg_id 16,
doc_id doc_title doc_order pg_id
12 c.zip 1 16
13 d.zip 2 16
3 f.doc 3 16
4 g.doc 4 16
But I get all of them intead!
doc_id doc_title doc_order pg_id
14 a.zip 1 NULL
15 b.zip 2 NULL
12 c.zip 1 16
13 d.zip 2 16
3 f.doc 3 16
4 g.doc 4 16
How can I fix this query,
SELECT *
FROM root_documents
WHERE (root_documents.pg_id = ? OR root_documents.pg_id IS NULL)
Or do I have to repeat the query like I usually do?
EDIT:
This answer looks strange to me when I have a parameter to be passed into the placeholder:
SELECT *
FROM root_documents
WHERE (root_documents.pg_id = '16' OR (root_documents.pg_id IS NULL AND '16' IS NULL))
and when without a parameter,
SELECT *
FROM root_documents
WHERE (root_documents.pg_id = NULL OR (root_documents.pg_id IS NULL AND NULL IS NULL))
Note that I pass NULL
into the placeholder
when no value is returned.
Sorry : you are using MYSQL... Disregard.
One way is this:
SELECT *
FROM root_documents
WHERE isnull(root_documents.pg_id, -1) = ?
-1 or any value you never expect to find in [root_documents.pg_id]. Then when you want to search for NULL query for that value.
I think you want something like this:
SELECT *
FROM root_documents
WHERE (root_documents.pg_id = ? OR (root_documents.pg_id IS NULL AND ? IS NULL))
You could set the ? to a number when you want that pg_id, or to the string IS NULL
when you want the ones that are null.
I think the following should work for you(updated):
SELECT *
FROM root_documents r1
WHERE (r1.pg_id = ?
OR (NOT EXISTS
(SELECT * FROM FROM root_documents r2
WHERE r2.pg_id = ? )
AND r1.pg_id IS NULL)
精彩评论