January 25, 2016 at 10:57 am
Below query will give me students that are registered to Dance School
SELECT DISTINCT
"Name"."ID",
"Name"."FULL_NAME",
"Name"."BIRTH_DATE",
"Name"."FULL_ADDRESS",
"Name"."EMAIL",
"Meet_Master"."TITLE",
"Meet_Master"."MEETING",
"Meet_Master"."BEGIN_DATE",
"Orders"."TOTAL_PAYMENTS",
"Orders"."STATUS",
"Meet_Master"."MEETING_TYPE",
"Orders"."ST_ID",
"Orders"."ORDER_DATE",
"vw_csys_ParentInfo"."Parent1_FullName",
"vw_csys_ParentInfo"."Parent1_Cell",
"vw_csys_ParentInfo"."Parent2_FullName",
"vw_csys_ParentInfo"."Parent2_Cell",
"vw_csys_ParentInfo"."Family_Email",
"vw_csys_ActualRegistration_Count"."Count",
"Meet_Master"."END_DATE",
"Order_Meet"."MEETING",
"Name"."PAID_THRU"
FROM (((((("IMIS"."dbo"."Name" "Name" INNER JOIN "IMIS"."dbo"."Orders" "Orders" ON "Name"."ID"="Orders"."ST_ID") LEFT OUTER JOIN "IMIS"."dbo"."vw_csys_ParentInfo" "vw_csys_ParentInfo" ON "Name"."ID"="vw_csys_ParentInfo"."id") INNER JOIN "IMIS"."dbo"."Order_Badge" "Order_Badge" ON "Orders"."ORDER_NUMBER"="Order_Badge"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."Order_Meet" "Order_Meet" ON "Order_Badge"."ORDER_NUMBER"="Order_Meet"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."Meet_Master" "Meet_Master" ON "Order_Meet"."MEETING"="Meet_Master"."MEETING") INNER JOIN "IMIS"."dbo"."Order_Lines" "Order_Lines" ON "Order_Meet"."ORDER_NUMBER"="Order_Lines"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."vw_csys_ActualRegistration_Count" "vw_csys_ActualRegistration_Count" ON "Order_Meet"."MEETING"="vw_csys_ActualRegistration_Count"."PRODUCT_MAJOR"
WHERE "Meet_Master"."MEETING_TYPE"='PADA' AND "Order_Lines"."PRODUCT_CODE" LIKE '%/MAIN'
ORDER BY "Meet_Master"."MEETING"
Below query will give me students registered for Music School.
SELECT DISTINCT
"Name"."ID",
"Name"."FULL_NAME",
"Name"."BIRTH_DATE",
"Name"."FULL_ADDRESS",
"Name"."EMAIL",
"Meet_Master"."TITLE",
"Meet_Master"."MEETING",
"Meet_Master"."BEGIN_DATE",
"Orders"."TOTAL_PAYMENTS",
"Orders"."STATUS",
"Meet_Master"."MEETING_TYPE",
"Orders"."ST_ID",
"Orders"."ORDER_DATE",
"vw_csys_ParentInfo"."Parent1_FullName",
"vw_csys_ParentInfo"."Parent1_Cell",
"vw_csys_ParentInfo"."Parent2_FullName",
"vw_csys_ParentInfo"."Parent2_Cell",
"vw_csys_ParentInfo"."Family_Email",
"vw_csys_ActualRegistration_Count"."Count",
"Meet_Master"."END_DATE",
"Order_Meet"."MEETING",
"Name"."PAID_THRU"
FROM (((((("IMIS"."dbo"."Name" "Name" INNER JOIN "IMIS"."dbo"."Orders" "Orders" ON "Name"."ID"="Orders"."ST_ID") LEFT OUTER JOIN "IMIS"."dbo"."vw_csys_ParentInfo" "vw_csys_ParentInfo" ON "Name"."ID"="vw_csys_ParentInfo"."id") INNER JOIN "IMIS"."dbo"."Order_Badge" "Order_Badge" ON "Orders"."ORDER_NUMBER"="Order_Badge"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."Order_Meet" "Order_Meet" ON "Order_Badge"."ORDER_NUMBER"="Order_Meet"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."Meet_Master" "Meet_Master" ON "Order_Meet"."MEETING"="Meet_Master"."MEETING") INNER JOIN "IMIS"."dbo"."Order_Lines" "Order_Lines" ON "Order_Meet"."ORDER_NUMBER"="Order_Lines"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."vw_csys_ActualRegistration_Count" "vw_csys_ActualRegistration_Count" ON "Order_Meet"."MEETING"="vw_csys_ActualRegistration_Count"."PRODUCT_MAJOR"
WHERE "Meet_Master"."MEETING_TYPE"='TSM' AND "Order_Lines"."PRODUCT_CODE" LIKE '%/MAIN'
ORDER BY "Meet_Master"."MEETING"
Now my requirement is this....
I need to indicate which FAMILIES have a student in the music school AND a student in the dance department?
Music School meeting_type = TSM
Dance Shool meeting_type = PADA
Any help or hint will be appreciated.
Regards,
SQLisAwe5oMe.
January 25, 2016 at 11:18 am
Looks like if you had a ParentID (the ID of the parent/family of the child) in each of the queries you listed, you could just inner join them on ParentID, and any ParentIDs without a record in both sets would "fall out". (Only the intersecting would be kept.)
You'd only need to return the ParentID and then you could join back to a "Parent" table, where you have the names of the children's parents.
January 25, 2016 at 12:40 pm
Change your criteria to MEETING_TYPE IN ('PADA', 'TSM'), then GROUP BY your parent info, HAVING COUNT(DISTINCT MEETING_TYPE) = 2
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 25, 2016 at 12:50 pm
drew.allen (1/25/2016)
Change your criteria to MEETING_TYPE IN ('PADA', 'TSM'), then GROUP BY your parent info, HAVING COUNT(DISTINCT MEETING_TYPE) = 2Drew
Hi Drew,
I didn't get the groupby caluse....can you put it in for me in the script.
SELECT DISTINCT
"Name"."ID",
"Name"."FULL_NAME",
"Name"."BIRTH_DATE",
"Name"."FULL_ADDRESS",
"Name"."EMAIL",
"Meet_Master"."TITLE",
"Meet_Master"."MEETING",
"Meet_Master"."BEGIN_DATE",
"Orders"."TOTAL_PAYMENTS",
"Orders"."STATUS",
"Meet_Master"."MEETING_TYPE",
"Orders"."ST_ID",
"Orders"."ORDER_DATE",
"vw_csys_ParentInfo"."Parent1_FullName",
"vw_csys_ParentInfo"."Parent1_Cell",
"vw_csys_ParentInfo"."Parent2_FullName",
"vw_csys_ParentInfo"."Parent2_Cell",
"vw_csys_ParentInfo"."Family_Email",
"vw_csys_ActualRegistration_Count"."Count",
"Meet_Master"."END_DATE",
"Order_Meet"."MEETING",
"Name"."PAID_THRU"
FROM (((((("IMIS"."dbo"."Name" "Name" INNER JOIN "IMIS"."dbo"."Orders" "Orders" ON "Name"."ID"="Orders"."ST_ID") LEFT OUTER JOIN "IMIS"."dbo"."vw_csys_ParentInfo" "vw_csys_ParentInfo" ON "Name"."ID"="vw_csys_ParentInfo"."id") INNER JOIN "IMIS"."dbo"."Order_Badge" "Order_Badge" ON "Orders"."ORDER_NUMBER"="Order_Badge"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."Order_Meet" "Order_Meet" ON "Order_Badge"."ORDER_NUMBER"="Order_Meet"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."Meet_Master" "Meet_Master" ON "Order_Meet"."MEETING"="Meet_Master"."MEETING") INNER JOIN "IMIS"."dbo"."Order_Lines" "Order_Lines" ON "Order_Meet"."ORDER_NUMBER"="Order_Lines"."ORDER_NUMBER") INNER JOIN "IMIS"."dbo"."vw_csys_ActualRegistration_Count" "vw_csys_ActualRegistration_Count" ON "Order_Meet"."MEETING"="vw_csys_ActualRegistration_Count"."PRODUCT_MAJOR"
WHERE "Meet_Master"."MEETING_TYPE" IN ('PADA', 'TSM') AND "Order_Lines"."PRODUCT_CODE" LIKE '%/MAIN'
ORDER BY "Meet_Master"."MEETING"
Regards,
SQLisAwe5oMe.
January 25, 2016 at 1:31 pm
Using aliases is a great idea. However when you name your alias the same as the original table name it kind of defeats the purpose. Also, you should avoid using double quotes around everything. It really makes your queries a lot harder to read than necessary. Then there were all the extra () rolled in the joins. Here is what your query would look like with more reasonable aliases and all the extra noise out of they way.
SELECT DISTINCT
n.ID,
n.FULL_NAME,
n.BIRTH_DATE,
n.FULL_ADDRESS,
n.EMAIL,
mm.TITLE,
mm.MEETING,
mm.BEGIN_DATE,
o.TOTAL_PAYMENTS,
o.[STATUS],
mm.MEETING_TYPE,
o.ST_ID,
o.ORDER_DATE,
p.Parent1_FullName,
p.Parent1_Cell,
p.Parent2_FullName,
p.Parent2_Cell,
p.Family_Email,
arc.[Count],
mm.END_DATE,
om.MEETING,
n.PAID_THRU
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
INNER JOIN IMIS.dbo.Order_Lines ol ON om.ORDER_NUMBER = ol.ORDER_NUMBER
INNER JOIN IMIS.dbo.vw_csys_ActualRegistration_Count arc ON om.MEETING = arc.PRODUCT_MAJOR
WHERE mm.MEETING_TYPE = 'PADA'
AND ol.PRODUCT_CODE LIKE '%/MAIN'
ORDER BY mm.MEETING
I had to wrap a couple of columns in square brackets because the column names are reserved words (something else that should be avoided). The above query is the same thing you originally posted but it is a LOT easier to what is going on.
As to the question at hand. Do you want families where one student is in both departments or just any student from each family that is in both departments? It is subtle but the difference is important.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2016 at 1:41 pm
Hi Sean, Thanks for the feedback.
Sorry for the messy query....I didn't actually write it.....I wish I could write query like that....but thanks for cleaning it up.
Anyway, I use crystal reports for reporting purpose and I was preparing the report and got the query output from crystal, thats why it looked messy with all the quotes, etc.
I need any student from each family that is in both departments.
Thanks again.
Regards,
SQLisAwe5oMe.
January 25, 2016 at 2:04 pm
SQLisAwE5OmE (1/25/2016)
Hi Sean, Thanks for the feedback.Sorry for the messy query....I didn't actually write it.....I wish I could write query like that....but thanks for cleaning it up.
Anyway, I use crystal reports for reporting purpose and I was preparing the report and got the query output from crystal, thats why it looked messy with all the quotes, etc.
I need any student from each family that is in both departments.
Thanks again.
Gotcha. Boy the sql generated from crystal hasn't gotten any better than the last time I used it (at least 15 years ago).
Given the clarification to the requirements the suggestion from Drew is the best way to tackle this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2016 at 2:09 pm
Sean Lange (1/25/2016)
SQLisAwE5OmE (1/25/2016)
Hi Sean, Thanks for the feedback.Sorry for the messy query....I didn't actually write it.....I wish I could write query like that....but thanks for cleaning it up.
Anyway, I use crystal reports for reporting purpose and I was preparing the report and got the query output from crystal, thats why it looked messy with all the quotes, etc.
I need any student from each family that is in both departments.
Thanks again.
Gotcha. Boy the sql generated from crystal hasn't gotten any better than the last time I used it (at least 15 years ago).
Given the clarification to the requirements the suggestion from Drew is the best way to tackle this.
Thanks but I was confused with what he mentioned about groupby clause
HAVING COUNT(DISTINCT MEETING_TYPE) = 2
can you clarify?
Regards,
SQLisAwe5oMe.
January 25, 2016 at 2:33 pm
SQLisAwE5OmE (1/25/2016)
Sean Lange (1/25/2016)
SQLisAwE5OmE (1/25/2016)
Hi Sean, Thanks for the feedback.Sorry for the messy query....I didn't actually write it.....I wish I could write query like that....but thanks for cleaning it up.
Anyway, I use crystal reports for reporting purpose and I was preparing the report and got the query output from crystal, thats why it looked messy with all the quotes, etc.
I need any student from each family that is in both departments.
Thanks again.
Gotcha. Boy the sql generated from crystal hasn't gotten any better than the last time I used it (at least 15 years ago).
Given the clarification to the requirements the suggestion from Drew is the best way to tackle this.
Thanks but I was confused with what he mentioned about groupby clause
HAVING COUNT(DISTINCT MEETING_TYPE) = 2
can you clarify?
There are several ways you could code but the basic gist of it is that you want only those parents where count of distinct MEETING_TYPE >= 2 (or maybe equals 2 if you want to limit the results to ONLY those families that have students in both of those departments and ONLY those departments). There are some challenges though because you talk about limiting to only those families (as derived by parent data) as being in those two departments but in your main query you have a left join to ParentInfo. There must be a reason you have a left join there or can it be changed to an inner join now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2016 at 2:51 pm
INNER join is fine at the parent info....
I am confused whats the exact syntax to use under group by clause.
Basically I need to find out if a student is registered to both music and dane school.
Parent 1 normally a father, parent 2 mother.
Regards,
SQLisAwe5oMe.
January 25, 2016 at 3:00 pm
Sean Lange (1/25/2016)
SQLisAwE5OmE (1/25/2016)
Hi Sean, Thanks for the feedback.Sorry for the messy query....I didn't actually write it.....I wish I could write query like that....but thanks for cleaning it up.
Anyway, I use crystal reports for reporting purpose and I was preparing the report and got the query output from crystal, thats why it looked messy with all the quotes, etc.
I need any student from each family that is in both departments.
Thanks again.
Gotcha. Boy the sql generated from crystal hasn't gotten any better than the last time I used it (at least 15 years ago).
Given the clarification to the requirements the suggestion from Drew is the best way to tackle this.
I hear you there. It's been longer than that for me and I sure don't miss it at all. Then again, I know what you're dealing with now so I'd imagine it must look like a piece of cake to you. 😉
OP, you can write queries like Sean's. Take a look at them - they do the same thing. The only difference is that one's readable and the other one contains lots of noise. When you write your FROM clause, take a second to give each table an alias. Then, when you're writing your column list you want to select, simply use the alias instead of the full table name. It'll take some getting-used-to if you aren't accustomed to it, but I think you'll find it much more intuitive and it won't take too long to adjust. You might also find that, given the increased maintainability of you code, you write faster.
The GROUP BY clause is how to you want to group your result set. You need to have one if you want to use the HAVING clause. The GROUP BY is covered at https://msdn.microsoft.com/en-us/library/ms177673.aspx.
January 25, 2016 at 3:10 pm
SQLisAwE5OmE (1/25/2016)
INNER join is fine at the parent info....I am confused whats the exact syntax to use under group by clause.
Basically I need to find out if a student is registered to both music and dane school.
Parent 1 normally a father, parent 2 mother.
Your requirements are still a little fuzzy here. Do you want students that are registered in both schools or students in a family that have students in both schools. The difference is subtle but incredibly important.
With no ddl to work and vague business rules this is a bit like hunting for squids in a black hole while wearing a blind fold and covered in peanut butter with a pack of wolves closing in.
This would be one way to write the sql to get a family that has at least one student (maybe not the same student) in both schools.
SELECT DISTINCT
n.ID,
n.FULL_NAME,
n.BIRTH_DATE,
n.FULL_ADDRESS,
n.EMAIL,
mm.TITLE,
mm.MEETING,
mm.BEGIN_DATE,
o.TOTAL_PAYMENTS,
o.[STATUS],
mm.MEETING_TYPE,
o.ST_ID,
o.ORDER_DATE,
p.Parent1_FullName,
p.Parent1_Cell,
p.Parent2_FullName,
p.Parent2_Cell,
p.Family_Email,
arc.[Count],
mm.END_DATE,
om.MEETING,
n.PAID_THRU
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
INNER JOIN IMIS.dbo.Order_Lines ol ON om.ORDER_NUMBER = ol.ORDER_NUMBER
INNER JOIN IMIS.dbo.vw_csys_ActualRegistration_Count arc ON om.MEETING = arc.PRODUCT_MAJOR
WHERE mm.MEETING_TYPE = 'PADA'
AND ol.PRODUCT_CODE LIKE '%/MAIN'
AND EXISTS
(
select pa.ParentID --no clue what the primary key is for this so I am guessing
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
where pa.ParentID = p.ParentID
and mm.MEETING_TYPE in ('TSM', 'PADA')
group by pa.ParentID
having COUNT(distinct MEETING_TYPE) = 2
)
ORDER BY mm.MEETING
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2016 at 3:12 pm
Ed Wagner (1/25/2016)
I know what you're dealing with now so I'd imagine it must look like a piece of cake to you. 😉
What I am dealing with now is why I haven't been around as much and also why I still MUST come around some. It is truly awful. The upside is we keep promising release dates and then trying to force the development to fit into that fantasy. Needless to say we have moved back the release date multiple times and the next one is going to be missed too but the powers that be have yet to spill the beans to the top.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 25, 2016 at 3:33 pm
Sean Lange (1/25/2016)
SQLisAwE5OmE (1/25/2016)
INNER join is fine at the parent info....I am confused whats the exact syntax to use under group by clause.
Basically I need to find out if a student is registered to both music and dane school.
Parent 1 normally a father, parent 2 mother.
Your requirements are still a little fuzzy here. Do you want students that are registered in both schools or students in a family that have students in both schools. The difference is subtle but incredibly important.
With no ddl to work and vague business rules this is a bit like hunting for squids in a black hole while wearing a blind fold and covered in peanut butter with a pack of wolves closing in.
This would be one way to write the sql to get a family that has at least one student (maybe not the same student) in both schools.
SELECT DISTINCT
n.ID,
n.FULL_NAME,
n.BIRTH_DATE,
n.FULL_ADDRESS,
n.EMAIL,
mm.TITLE,
mm.MEETING,
mm.BEGIN_DATE,
o.TOTAL_PAYMENTS,
o.[STATUS],
mm.MEETING_TYPE,
o.ST_ID,
o.ORDER_DATE,
p.Parent1_FullName,
p.Parent1_Cell,
p.Parent2_FullName,
p.Parent2_Cell,
p.Family_Email,
arc.[Count],
mm.END_DATE,
om.MEETING,
n.PAID_THRU
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
INNER JOIN IMIS.dbo.Order_Lines ol ON om.ORDER_NUMBER = ol.ORDER_NUMBER
INNER JOIN IMIS.dbo.vw_csys_ActualRegistration_Count arc ON om.MEETING = arc.PRODUCT_MAJOR
WHERE mm.MEETING_TYPE = 'PADA'
AND ol.PRODUCT_CODE LIKE '%/MAIN'
AND EXISTS
(
select pa.ParentID --no clue what the primary key is for this so I am guessing
FROM IMIS.dbo.Name n
INNER JOIN IMIS.dbo.Orders o ON n.ID = o.ST_ID
LEFT OUTER JOIN IMIS.dbo.vw_csys_ParentInfo p ON n.ID = p.id
INNER JOIN IMIS.dbo.Order_Badge ob ON o.ORDER_NUMBER = ob.ORDER_NUMBER
INNER JOIN IMIS.dbo.Order_Meet om ON ob.ORDER_NUMBER = om.ORDER_NUMBER
INNER JOIN IMIS.dbo.Meet_Master mm ON om.MEETING = mm.MEETING
where pa.ParentID = p.ParentID
and mm.MEETING_TYPE in ('TSM', 'PADA')
group by pa.ParentID
having COUNT(distinct MEETING_TYPE) = 2
)
ORDER BY mm.MEETING
Here is the create script for view [dbo].[vw_csys_ParentInfo].
CREATE view [dbo].[vw_csys_ParentInfo]
as
select n.id, n.first_name, n.middle_name, n.last_name, n.full_name, n.last_first, n.co_id, n.company, n.email,
n.home_phone, n.toll_free Cell, i.gender, i.age, i.birthdate, i.grade, i.school,
nmp.id Parent1_id, nmp.first_name Parent1_FirstName, nmp.middle_name Parent1_MiddleName, nmp.last_name Parent1_LastName,
nmp.full_name Parent1_FullName, nmp.last_first Parent1_LastFirst, nmp.email Parent1_Email,
nmp.home_phone Parent1_HomePhome, nmp.work_phone Parent1_WorkPhone, nmp.toll_free Parent1_Cell, imp.gender Parent1_Gender,
nsp.id Parent2_ID, nsp.first_name Parent2_FirstName, nsp.middle_name Parent2_MiddleName, nsp.last_name Parent2_LastName,
nsp.full_name Parent2_FullName, nsp.last_first Parent2_LastFirst, nsp.email Parent2_Email,
nsp.home_phone Parent2_HomePhome, nsp.work_phone Parent2_WorkPhone, nsp.toll_free Parent2_Cell, isp.gender Parent2_Gender,
nf.email Family_Email,
nampw.email Parent1_WorkEmail, naspw.email Parent2_WorkEmail, naw.email WorkEmail, nafw.email Family_WorkEmail,
namps.email Parent1_SeasonalEmail, nasps.email Parent2_SeasonalEmail, nas.email SeasonalEmail, nafs.email Family_SeasonalEmail
from name n
left join name nmp on nmp.co_id = n.co_id and nmp.member_type like '%MP'--member primary
left join name nsp on nsp.co_id = n.co_id and nsp.member_type like '%SP'--member spouse
left join name nf on nf.id = n.co_id --family
left join individual_JCCOTP i on i.id = n.id --child
left join individual_JCCOTP imp on imp.id = nmp.id--member primary individual
left join individual_JCCOTP isp on isp.id =nsp.id--member spouse individual
left join Name_Address nampw on nmp.ID = nampw.ID and nampw.PURPOSE = 'Work'--primary work address
left join Name_Address naspw on nsp.ID = naspw.ID and naspw.PURPOSE = 'Work'--spouse work address
left join Name_Address naw on n.ID = naw.ID and naw.PURPOSE = 'Work'--child work address
left join Name_Address nafw on nf.ID = nafw.ID and nafw.PURPOSE = 'Work'--family work address
left join Name_Address namps on nmp.ID = namps.ID and namps.PURPOSE = 'Seasonal' --primary seasonal address
left join Name_Address nasps on nsp.ID = nasps.ID and nasps.PURPOSE = 'Seasonal' --spouse seasonal address
left join Name_Address nas on n.ID = nas.ID and nas.PURPOSE = 'Seasonal'--child seasonal address
left join Name_Address nafs on nf.ID = nafs.ID and nafs.PURPOSE = 'Seasonal'--family seasonal address
where n.MEMBER_TYPE like '%CH%' or n.CATEGORY like '%teens%'
GO
Regards,
SQLisAwe5oMe.
January 25, 2016 at 3:38 pm
Now we're starting to get somewhere... We still need a couple of things: The create table scripts for the tables referenced in the views. Without them, it's impossible to know what keys to join on/group on, because we don't know what are the primary and foreign keys of the tables underlying the view you posted.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply