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