Sean Lange (2/4/2016)
SQLisAwE5OmE (2/4/2016)
Sean Lange (2/4/2016)
SQLisAwE5OmE (2/4/2016)
Hi Sean,
I tried your query, and I am getting the following error.
Msg 4104, Level 16, State 1, Line 43
The multi-part identifier "pa.ParentID" could not be bound.
Msg 207, Level 16, State 1, Line 43
Invalid column name 'ParentID'.
Msg 4104, Level 16, State 1, Line 45
The multi-part identifier "pa.ParentID" could not be bound.
Msg 4104, Level 16, State 1, Line 36
The multi-part identifier "pa.ParentID" could not be bound.
Let me know if you need the create script for any other table.
Did you see my comment?
select pa.ParentID --no clue what the primary key is for this so I am guessing
I have no idea what the key is. I am a bit scared that you can't convert that into your environment. I get the feeling you don't understand this code and are just trying it out. The important part of this is understanding what the code is doing. Once that happens the adjustment should be simple.
I understand the first part of the query.....second part I am confused
There is no parent ID on Name table....
The way you differentiate Parent1, parent2 is from Member_Type column
Member_Type = MP, NM-MP = Parent1
Member_Type = MSP, NP-SP = Parent2
column ID is the primary key column.
Right so change the code I took a shot in the dark with and replace ParentID with column(s) that are your primary key. I guess ID?
Hi Sean,
I change the code according to what you suggested, but this query only listing out students with registered to Dance school(Meeting type = PADA)
I was thinking, the results should be like, 2 records for each student, 1 for TSM and 1 for PADA.
My requirement is, I need to list all students that are registered to both music(TSM) and dacne(PADA) school.
Let me know if you see if you see anything wrong in the script.
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 n.ID
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 n.ID = p.id
and mm.MEETING_TYPE in ('TSM', 'PADA')
group by n.ID
having COUNT(distinct MEETING_TYPE) = 2
)
ORDER BY mm.MEETING
Regards,
SQLisAwe5oMe.