February 24, 2015 at 11:47 am
Hi - I have the following tables:
tbl_Person (personID, first_name, lastname)
tbl_student (studentID)
tbl_stupar (personID, StudentID, relationship)
tbl_person_Phone (personID, phone)
I need to list all students who have both parents phone number is null. The parent relationship values 1 and 2 indicates the person is either Mom (value 2) or dad (value 1) of the student. Note: I am using student parent as an example to write my query.
February 24, 2015 at 12:02 pm
What have you tried? Why would you have a row with a phone set as null instead of not having a row at all?
You could at least post DDL, sample data and expected results in a consumable format.
February 24, 2015 at 12:08 pm
Luis Cazares (2/24/2015)
What have you tried? Why would you have a row with a phone set as null instead of not having a row at all?You could at least post DDL, sample data and expected results in a consumable format.
I was going to ask the second question as well, but I think my code will handle that:
DECLARE @Person TABLE
(
PersonID INT IDENTITY(1, 1),
FirstName VARCHAR(20),
LastName VARCHAR(20)
);
DECLARE @Student TABLE
(
StudentID INT IDENTITY(1, 1)
);
DECLARE @StudentParent TABLE
(
PersonID INT,
StudentID INT,
Relationship TINYINT
);
DECLARE @PersonPhone TABLE
(
PersonID INT,
Phone VARCHAR(10)
);
SELECT
*
FROM
@Student AS S
WHERE
NOT EXISTS ( SELECT
1
FROM
@StudentParent AS SP
LEFT JOIN @PersonPhone AS PP
ON SP.PersonID = PP.PersonID
WHERE
S.StudentID = SP.StudentID AND
PP.Phone IS NOT NULL );
The final query basically says return the students where there isn't a row non-null row in PersonPhone which means you don't have a phone number for any parent for that student.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 24, 2015 at 1:43 pm
I didn't want to post any code because this seems a lot like homework.
February 24, 2015 at 1:46 pm
Jack - Many thanks to you, the query worked great.
Note: As I stated, I am using student/parent tbls scenario as an example. Once again, thanks.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply