Help with a query, plz

Question

Post reply

Help with a query, plz

SQLisAwe5oMe

SSChampion

Points: 14047
More actions
December 29, 2015 at 12:39 pm

#323685

Here is what I am trying to achieve,
I need to be able to pull all parent, who are females.
there is this view called dbo.vw_csys_NurserySchool and there are parent1 & Parent2 columns, but both columns have Male & Female, but I only want to pull where Parent1_Gender = 'F' and Parent2_Gender = 'F'
how can I include that in the query. Thanks.
Select Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, Parent2_ID, Parent2_FullName, Parent2_Email, Parent2_STATUS, Parent2_Gender, FULL_ADDRESS
from dbo.vw_csys_NurserySchool
Regards,
SQLisAwe5oMe.

Viewing 15 posts - 1 through 15 (of 37 total)

You must be logged in to reply to this topic. Login to reply

Alan Burstein SSC Guru Points: 61136 More actions · Answer 1

I'm guessing you would add this to the end of your query:

WHERE Parent1_Gender = 'F' and Parent2_Gender = 'F'

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 2

no, that will only give me the values both columns have Females.

but my requirement is to list all the parent with 'F' value from both tables.

not sure if I am clear to you?

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 3

***Correction...all the parent with 'F' value from both tables.

I meant to say parent with 'F' value from both columns.

Regards,
SQLisAwe5oMe.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

This should help:

https://msdn.microsoft.com/en-us/library/ms189773.aspx

Unless what you're looking for is the UNION ALL or UNPIVOT.

For better help, read the article linked in my signature.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 5

just to be clear...what records do you want returned....you have not provided any sample data so...here is a quick mock up

CREATE TABLE NurserySchool(

ID INTEGER

,Parent1_Gender VARCHAR(1)

,Parent2_Gender VARCHAR(1)

);

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (1,'M',NULL);

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (2,'F',NULL);

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (3,NULL,'M');

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (4,NULL,'F');

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (5,'M','M');

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (6,'M','F');

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (7,'F','M');

INSERT INTO NurserySchool(ID,Parent1_Gender,Parent2_Gender) VALUES (8,'F','F');

SELECT ID,

Parent1_Gender,

Parent2_Gender

FROM NurserySchool;

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Jeff Moden SSC Guru Points: 1003965 More actions · Answer 6

I'm thinking that all you need to do is change the AND to an OR in Alan's WHERE clause.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 7

Take a look at the data J Livingston has posted.

Do you want all rows where there's an F in both columns? Alan's code does this.

Do you want all rows where there's an F in one of the columns?

Or do you want something different?

If you're after something different, then please try to explain it. If, for example, you want ParentName1 if they're female combined with ParentName2 if they're female, please tell us that. We can't see what you see. All we have to go on is what you tell us.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 8

Thank you guys, I really appreciate the efforts.

I think, I probably write it wrong.....basically both of these columns 'Parent1_Gender' and 'Parent2_Gender' has both 'M' and 'F' values.

I want only to list all 'F' values combined with both of these columns. How to achieve that?

Select Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, Parent2_ID, Parent2_FullName, Parent2_Email, Parent2_STATUS, Parent2_Gender, FULL_ADDRESS

from dbo.vw_csys_NurserySchool

WHERE Parent1_Gender = 'F' or Parent2_Gender = 'F'

When I try with 'and', it gives me values where both columns have 'F' value, which is way wrong, and when I try with 'or', it gives me both 'M' & 'F' values

Regards,
SQLisAwe5oMe.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 9

It's still not at all clear what you're wanting.

If you could give us sample data and your expected results from that sample data, we'd probably be able to solve this very quickly.

To throw my wild guess in the ring, are you maybe wanting to just return a list of parents that have gender='F', with one parent and that parent's information per row?

Something like this?

CREATE TABLE #parents (parent1_id INT,

parent1_fullname VARCHAR(50),

parent1_email VARCHAR(50),

parent1_gender CHAR(1),

parent2_id INT,

parent2_fullname VARCHAR(50),

parent2_email VARCHAR(50),

parent2_gender CHAR(1)

);

INSERT INTO #parents VALUES

(1,'John Smith','JohnSmith@email.com','M',2,'Jane Doe','JaneDoe@provider.org','F'),

(3,'John Doe','John_Doe@mail.gov','M',4,'Jane Smith','JSmith@provider.edu','F'),

(5,'Billy George','beegee@somemail.biz','M',6,'Taz Devil','TDawg@acme.com','M'),

(7,'Petunia Pig','Petunias4Ever@yoohoo.com','F',8,'Lola Bunny','BugsIsGreat@toons.com','F');

SELECT parent_id=CASE WHEN parent=1 THEN parent1_id ELSE parent2_id END,

parent_fullname=CASE WHEN parent=1 THEN parent1_fullname ELSE parent2_fullname END,

parent_email=CASE WHEN parent=1 THEN parent1_email ELSE parent2_email END,

parent_gender='F'

FROM #parents p

CROSS APPLY (VALUES (1),(2)) x (parent)

WHERE CASE WHEN parent=1 THEN parent1_gender ELSE parent2_gender END='F';

DROP TABLE #parents;

At any rate, please give us some sample data and your expected results. Alternatively, you could just tell us what results you expect from the two sets of sample data we've created. That will help solve this much more quickly than our guessing. 🙂

Cheers!

ScottPletcher SSC Guru Points: 100980 More actions · Answer 10

Select females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

SELECT Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, FULL_ADDRESS

WHERE Parent1_Gender = 'F'

UNION ALL

SELECT Parent2_id, Parent2_FullName, Parent2_Email, Parent2_STATUS, Parent2_Gender, FULL_ADDRESS

WHERE Parent2_Gender = 'F'

) AS females_only

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 11

Thanks Scott, that worked but I do see duplicates. I tried to add distinct in the query and still see duplicates, any idea how to remove duplicates?

Select females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

SELECT distinct Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, FULL_ADDRESS

WHERE Parent1_Gender = 'F'

UNION ALL

SELECT distinct Parent2_id, Parent2_FullName, Parent2_Email, Parent2_STATUS, Parent2_Gender, FULL_ADDRESS

WHERE Parent2_Gender = 'F'

) AS females_only

Regards,
SQLisAwe5oMe.

ScottPletcher SSC Guru Points: 100980 More actions · Answer 12

Change the UNION ALL to just UNION. But the only way you'd have dups is if parent1 and parent2 both contained the same data.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 13

Thanks Scott, it still gives duplicates even after changing as you mentioned.

Anyway, thank you, at least I've got the list, I will export to excel and remove duplicates.

Thank you again all.

Regards,
SQLisAwe5oMe.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 14

There would also be duplicates if the same parent occurred on different rows in vw_csys_NurserySchool. You could just do a DISTINCT in the outer SELECT (i.e., SELECT DISTINCT females_only.*) to remove them.

Cheers!