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.