Help with a query, plz

Question

Post reply

Help with a query, plz

Viewing 15 posts - 16 through 30 (of 37 total)

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 1

Yes, I tried with distinct as I mentioned earlier, but still duplicates shows.

Unless I am inserting distinct at wrong place. See below

Select females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

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

WHERE Parent1_Gender = 'F'

UNION

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

WHERE Parent2_Gender = 'F'

) AS females_only

Regards,
SQLisAwe5oMe.

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

Read my post a bit more closely 🙂

Jacob Wilkins (12/29/2015)
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!

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 3

I am really sorry Jacob. Thank you!....that seems working now.

SELECT DISTINCT females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

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

WHERE Parent1_Gender = 'F'

UNION ALL

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

WHERE Parent2_Gender = 'F'

) AS females_only

Regards,
SQLisAwe5oMe.

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

No need to be too sorry; we all skip over things when reading sometimes. Goodness knows I have 🙂

I'm glad it's working for you!

Cheers!

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

Jeff Moden (12/29/2015)
~~I'm thinking that all you need to do is change the AND to an OR in Alan's WHERE clause~~.

Never mind and apologies. I was in a hurry and didn't notice that this ~~table~~ view is denormalized.

--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)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 6

Thnx again Jeff for trying. I am learning.

Regards,
SQLisAwe5oMe.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

SQLisAwE5OmE (12/29/2015)
Thnx again Jeff for trying. I am learning.

No problem. Looking back at this, I see that it's not actually a table. It's a view. What is the code for the view? Life could be a whole lot easier if the underlying tables were actually properly normalized. If you get the chance, could you post the CREATE VIEW code?

--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)

Alan Burstein SSC Guru Points: 61141 More actions · Answer 8

SQLisAwE5OmE (12/29/2015)
Thnx again Jeff for trying. I am learning.

You've come to the best place for people that are learning SQL IMHO.

"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: 14052 More actions · Answer 9

Jeff, See the attachment for the create view script. Thanks.

Regards,
SQLisAwe5oMe.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

SQLisAwE5OmE (12/29/2015)
Jeff, See the attachment for the create view script. Thanks.

Thanks for that. That view is a bit of a "Rosetta Stone" for what the table relationships are.

I don't know everything about your data but, from what I saw in the view, the following should do the trick without all that extra overhead from the view. Of course, I have no way of testing this so check carefully.

SELECT ParentID = n.ID

,FullName = n.FULL_NAME

,EMail = n.EMAIL

,[Status] = n.[STATUS]

,Gender = i.GENDER

,[YEAR] = ns.[YEAR]

,FullAddress = na.FULL_ADDRESS

FROM dbo.NAME AS n

JOIN dbo.Individual_JCCOTP AS i ON i.ID = n.ID

JOIN dbo.NURSERY_SCHOOL AS ns ON ns.ID = n.ID

JOIN dbo.Name_Address AS na ON na.ADDRESS_NUM = n.MAIL_ADDRESS_NUM

JOIN dbo.Gen_Tables AS gt ON gt.CODE = ns.NS_PROGRAM

WHERE i.GENDER = 'F'

AND ns.[YEAR] > ''

AND ns.NS_PROGRAM > ''

AND gt.TABLE_NAME = 'NS_PROGRAM'

;

--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)

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 11

Jeff Moden (12/29/2015)
SQLisAwE5OmE (12/29/2015)
Jeff, See the attachment for the create view script. Thanks.
Thanks for that. That view is a bit of a "Rosetta Stone" for what the table relationships are.
I don't know everything about your data but, from what I saw in the view, the following should do the trick without all that extra overhead from the view. Of course, I have no way of testing this so check carefully.
SELECT ParentID = n.ID
,FullName = n.FULL_NAME
,EMail = n.EMAIL
,[Status] = n.[STATUS]
,Gender = i.GENDER
,[YEAR] = ns.[YEAR]
,FullAddress = na.FULL_ADDRESS
FROM dbo.NAME AS n
JOIN dbo.Individual_JCCOTP AS i ON i.ID = n.ID
JOIN dbo.NURSERY_SCHOOL AS ns ON ns.ID = n.ID
JOIN dbo.Name_Address AS na ON na.ADDRESS_NUM = n.MAIL_ADDRESS_NUM
JOIN dbo.Gen_Tables AS gt ON gt.CODE = ns.NS_PROGRAM
WHERE i.GENDER = 'F'
AND ns.[YEAR] > ''
AND ns.NS_PROGRAM > ''
AND gt.TABLE_NAME = 'NS_PROGRAM'
;

Hi Jeff,

This seems working but I am getting less rows(1448) Vs the previous results(2379)...so, I am not sure which is accurate now

Regards,
SQLisAwe5oMe.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 12

SQLisAwE5OmE (12/30/2015)
Jeff Moden (12/29/2015)
SQLisAwE5OmE (12/29/2015)
Jeff, See the attachment for the create view script. Thanks.
Thanks for that. That view is a bit of a "Rosetta Stone" for what the table relationships are.
I don't know everything about your data but, from what I saw in the view, the following should do the trick without all that extra overhead from the view. Of course, I have no way of testing this so check carefully.
SELECT ParentID = n.ID
,FullName = n.FULL_NAME
,EMail = n.EMAIL
,[Status] = n.[STATUS]
,Gender = i.GENDER
,[YEAR] = ns.[YEAR]
,FullAddress = na.FULL_ADDRESS
FROM dbo.NAME AS n
JOIN dbo.Individual_JCCOTP AS i ON i.ID = n.ID
JOIN dbo.NURSERY_SCHOOL AS ns ON ns.ID = n.ID
JOIN dbo.Name_Address AS na ON na.ADDRESS_NUM = n.MAIL_ADDRESS_NUM
JOIN dbo.Gen_Tables AS gt ON gt.CODE = ns.NS_PROGRAM
WHERE i.GENDER = 'F'
AND ns.[YEAR] > ''
AND ns.NS_PROGRAM > ''
AND gt.TABLE_NAME = 'NS_PROGRAM'
;
Hi Jeff,
This seems working but I am getting less rows(1448) Vs the previous results(2379)...so, I am not sure which is accurate now

Weren't the previous results showing duplicates?

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 13

Not after I modified the script as Alan mentioned as below.

SELECT DISTINCT females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

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

WHERE Parent1_Gender = 'F'

UNION ALL

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

WHERE Parent2_Gender = 'F'

) AS females_only

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 14

SQLisAwE5OmE (12/30/2015)
Not after I modified the script as Alan mentioned as below.
SELECT DISTINCT females_only.*
From dbo.vw_csys_NurserySchool
Cross Apply (
SELECT Parent1_id, Parent1_FullName, Parent1_Email, Parent1_STATUS, Parent1_Gender, YEAR, FULL_ADDRESS
WHERE Parent1_Gender = 'F'
UNION ALL
SELECT Parent2_id, Parent2_FullName, Parent2_Email, Parent2_STATUS, Parent2_Gender,YEAR, FULL_ADDRESS
WHERE Parent2_Gender = 'F'
) AS females_only

Another question.

The above query results include staff members as well and I want to exclude staff members.

There is a table called dbo.Name and the column CATEGORY = 'STF' identify the staff members.

How can I modify the script above to exclude n.CATEGORY <>'STF'.....so, my result will be excluding staff members. Thanks.

Regards,
SQLisAwe5oMe.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 15

SELECT DISTINCT females_only.*

From dbo.vw_csys_NurserySchool

Cross Apply (

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

WHERE Parent1_Gender = 'F' AND CATEGORY <> 'STF'

UNION

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

WHERE Parent2_Gender = 'F' AND CATEGORY <> 'STF'

) AS females_only

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".