Sql-server – Retrieve data from two tables where table 2 has multiple entries for one value

sql server

I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures:

Table 1           | Table 2
------------------+----------------------------------
Table1.Customerid |
                  | Table2.Customerid | Table2.Userid
                  | Table2.Customerid | Table2.Userid

Basically, I have in table 2, multiple records showing assignment of multiple userids to one customerid. On the page this data displays, I'm getting duplicate Customer IDs because there are multiple entries for that customer id in table 2.

How do I display the customer ID only once even if there are multiple entries in table 2?

Here is a sample of the query currently being executed with an additional search parameter in place for the company name: 

SELECT '' as campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, * 
FROM customers C 
LEFT OUTER JOIN customer_users CU on C.customerid = CU.customerid 
WHERE C.customerid > 0 
AND (C.company like '%blue%' OR c.altcompanyname like '%blue%') 
AND status = 0 
GROUP BY
    c.customerid,c.company,c.firstname,C.lastname,C.address1,C.address2,
    C.city,C.state,C.zip,C.country,C.phone,C.fax,C.cellphone,
    C.reference1,C.reference2,C.groupid,C.email,C.residential,C.status,
    C.datecreated,C.campaigndays, C.campaignstartdate, C.campaignenddate,
    C.lastupdate, C.lastupdateuser, C.campaignactive, C.customerstatus,
    C.regionid, C.categoryid,C.viewed,C.altcompanyname,C.url,cu.id,
    cu.customerid,C.lockaccount,cu.userid, cu.request, cu.regionid,
    cu.primaryrep, cu.dateadded 
ORDER BY C.company ASC

What I am looking for is this; no matter how many entries any customerid has in the customer_users table, I only want the distinct customer id's to be returned from the query. When a user searches the customers table, I don't want duplicate records in my gridview just because there are multiple users assigned to the customer.

Best Answer

Provided customer_users has ID and the order needed is customers.customerid , customer_users.ID you may ask sql server to show NULL in all but first row in a partition. Adjust ordering as needed.

SELECT '' AS campaigndaysremaining, COUNT(*) OVER () AS totalrecordcount, *,
  [show CustomerId]=CASE ROW_NUMBER() OVER (PARTITION BY C.customerid ORDER BY CU.ID) WHEN 1 THEN C.customerid END
FROM customers C 
LEFT OUTER JOIN customer_users CU ON C.customerid = CU.customerid 
WHERE C.customerid > 0
ORDER BY C.customerid , CU.ID

Getting only one user is a bit different task. First option is OUTER APPLY to get this only user 

SELECT '' as campaigndaysremaining, * 
FROM customers C 
OUTER APPLY (
  SELECT TOP(1) cu.userid, cu.request, cu.regionid, cu.primaryrep, cu.dateadded ,
     COUNT(*) OVER () AS totalrecordcount
  FROM customer_users CU 
  WHERE C.customerid = CU.customerid 
  ORDER BY cu.userid -- change ordering as needed
) CU
WHERE C.customerid > 0 AND (C.company like '%blue%' OR c.altcompanyname like '%blue%') AND status = 0 
ORDER BY C.company ASC
Related Question