May 21, 2009 at 5:58 am
Hello all!
I'm trying to create a stored procedure that needs to return only persons that do not have one or more of these fields null or empty.
Here you can see the 3 tables that will be involved in the query:
Persons - This table contains persons, and it has a PersonID field, this field is a foreign key in all the tables in this solution
Passport - This table has 2 of the columns I need to check if it has values or not
Tax - This table has 1 field I also need to check if it has a value or not
The values I need to check is:
Passport.ExpiryDate - DateTime - This will be NULL if no value is available
Passport.DuffNr - Nvarchar(50) - This will be empty (not null) if no value is available
Tax.Dnr - Nvarchar(50) - This will be empty (not null) if no value is available
So, I need to create a stored procedure that will return * from persons that have one or more of these fields null or empty.
Can someone please kick me in the right direction? I have all the code for querying and receiving the query ready, it's only the sql part I'm not sure on..
May 21, 2009 at 6:34 am
An empty string check is simply STRING=''
A Null check is STRING is NULL
But to check for either you can use ISNULL(STRING,'')=''
From your description something along these lines may work.
CREATE PROCEDURE dbo.ProcedureName
AS
SELECT P.*
FROM Persons AS P
JOIN Passport AS PP ON P.PersonID = PP.fk_PersonID
JOIN Tax AS T ON P.PersonID = T.fk_PersonID
WHERE PP.ExpiryDate is NULL
OR PP.DuffNr = ''
OR T.Dnr = ''
But - will every person have a passport record AND a tax record? e.g if someone has no tax record, do you want to count it as an empty Tax.Dnr, or ignore it completely.
May 21, 2009 at 8:10 am
The thing is, that it might be persons that still have no Passport or Tax records. How can I handle that in the sql query?
May 21, 2009 at 9:39 am
Well what output is required if a person has no passport record, or no tax record. - Do you include them in the results - i.e. treat it the same as if they had a record with a null date, or empty nvarchar ? or not?
May 21, 2009 at 10:51 am
The background for this query is: I have a gridview that is going to show persons that lack one or more of these 3 fields. This is because many persons that get created, is not "finished" until 1-2 weeks later. Then I have to be able to check this gridview, and all the persons lacking one of these fields will appear.
So, I only need the data from the persons table as output, no need for anything else.
I'm sorry if it is bad explained, do you understand?
May 21, 2009 at 11:14 am
Actually, it's working as you wrote it, as long as the records are made in the 2 tables (so that the foreign key is in place in a row), passport and tax. But I also need to get them in the resultset if they have not yet been created in these tables. I have no idea on how to check for this..
May 21, 2009 at 11:34 am
I made it! 🙂
I just had to add LEFT OUTER JOIN instead of only JOIN.
Thank you very much for your help Tom, very nice of you! 🙂
May 21, 2009 at 12:43 pm
Good! 😀
I nearly suggested the OUTER JOIN, but instead I ask questions to make you think.
If you realise if yourself, the knowledge sticks better.
😉
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply