Help with a query, plz

Question

Help with a query, plz

Viewing 8 posts - 31 through 37 (of 37 total)

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

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 1

ScottPletcher (12/30/2015)
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

Thanks Scott, but CATEGORY is not a column in dbo.vw_csys_NurserySchool, it's a column from dbo.Name table.....I have attached the create script for the dbo.Name table.

How can I join this view dbo.vw_csys_NurserySchool and table dbo.Name to get the results?

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

I tried to include the CATEGORY field to the script Jeff provided and it seems working, but my only concern is, this doesn't pull the parent2 field....as my original script....so, I think that is why the results are less.

How can I include the parent2 field to the query? I have attached create script for both table and view.

SELECT ParentID = n.ID

,FullName = n.FULL_NAME

,EMail = n.EMAIL

,[Status] = n.[STATUS]

,Gender = i.GENDER

,[YEAR] = ns.[YEAR]

,FullAddress = na.FULL_ADDRESS

,CATEGORY = n.CATEGORY

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'

and n.CATEGORY <>'STF'

;

Regards,
SQLisAwe5oMe.

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

SQLisAwE5OmE (12/30/2015)
I tried to include the CATEGORY field to the script Jeff provided and it seems working, but my only concern is, this doesn't pull the parent2 field....as my original script....so, I think that is why the results are less.

It doesn't need to pull the parent2 fields. Those were pulled mostly from the name table only when they had some value lie %SP. The query I wrote picks all the data from the single name table, which was duplicated in the view to make it look like you had two different sets of "parent" fields when there's really only one set.

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

Jeff Moden (12/30/2015)
SQLisAwE5OmE (12/30/2015)
I tried to include the CATEGORY field to the script Jeff provided and it seems working, but my only concern is, this doesn't pull the parent2 field....as my original script....so, I think that is why the results are less.
It doesn't need to pull the parent2 fields. Those were pulled mostly from the name table only when they had some value lie %SP. The query I wrote picks all the data from the single name table, which was duplicated in the view to make it look like you had two different sets of "parent" fields when there's really only one set.

But we noticed the Full_name field pulling some student as well, instead of parent....how are you differentiating to pull parent name?

Regards,
SQLisAwe5oMe.

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

SQLisAwE5OmE (12/30/2015)
Jeff Moden (12/30/2015)
SQLisAwE5OmE (12/30/2015)
I tried to include the CATEGORY field to the script Jeff provided and it seems working, but my only concern is, this doesn't pull the parent2 field....as my original script....so, I think that is why the results are less.
It doesn't need to pull the parent2 fields. Those were pulled mostly from the name table only when they had some value lie %SP. The query I wrote picks all the data from the single name table, which was duplicated in the view to make it look like you had two different sets of "parent" fields when there's really only one set.
But we noticed the Full_name field pulling some student as well, instead of parent....how are you differentiating to pull parent name?

That's a different question than before. The answer is, I'm not. I don't know your data and I didn't see anything in the view that would identify only parents EXCEPT maybe for the %MP and %SP filters in the view. I also didn't know that both students and parents were in the same table, although it certainly makes sense that they are.

To filter only on parents, you may need to add the table that the %MP and %SP filters are associated with.

My goal was to demonstrate that you don't necessarily need to use the view and unpivot and etc. It will be more efficient to call the Name table just once instead of 3 times like the view does. All we need to finish doing is understand what's in which tables and what filters we need. You've already identified that students are coming back in the code I provided. You must now have some knowledge of how to identify only the parents. Add that filter and the necessary join to the correct table. I just don't have the knowledge of you data nor access to gain that knowledge. You do. 🙂

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

Okay, Thanks for your inputs. Appreciate it.

Regards,
SQLisAwe5oMe.

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 7

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

The above script is working good, but i would like to know how can i join another table(dbo.Name) to pull the column 'Category' <> 'STF' ?

Regards,
SQLisAwe5oMe.

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

You need to look at your requirements and then really look at your data. The view pulls together some data, but you need filters you don't have, so go against the table instead. Your know your table and data. How does your table identify staff, parents and students? From that, you should be able to add in the filters into your WHERE clause to pull the data you want.

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