1

Quick query I need a hand with, a should know how to do this but im drawing a mental blank.

I have the following query which shows the right equipment cards for all the customers EXCEPT those that have 2 x subscriptions from the @SWA_CT_SUBSCR table. I need to only limit 1 subscription in my join so i dont get a double up of records for those with more than 1 subscription.

SELECT T0.[customer], T0.[custmrName], T0.[itemCode], T0.[itemName], T0.[insID] FROM OINS T0 INNER JOIN [dbo].[@SWA_CT_SUBSCR]  T2 ON T2.U_CardCode = T0.customer

I know it will be a subquery in the join but as mentioned above im drawing a mental blank on where it will be.

Thanks for any help offered.

Nick Jones
  • 93
  • 8

1 Answers1

0

Had some lunch, came back and figured it out.

SELECT T0.[customer], T0.[custmrName], T0.[itemCode], T0.[itemName], T0.[insID] FROM OINS T0 INNER JOIN [dbo].[@SWA_CT_SUBSCR] T2 ON T2.U_CardCode = T0.customer AND T2.Code = (SELECT TOP 1 A.Code FROM [dbo].[@SWA_CT_SUBSCR] A WHERE A.U_CardCode = T0.customer)

Nick Jones
  • 93
  • 8