NEED HELP WITH QUERY

I am trying to setup a query using SqlDataSource for DropDownList

This is my problem

Table 1 - LocationID, LocationName, CustomerID

Table 2 - ContactID, LocationID

I am trying to query Table 1 where the data is not in Table 2

Select LocationID, LocationName from Table 1 WHERE LocationID is not in Table 2 AND Table1.CustomerID = 1

LocationName = Text, LocationID = Value

The Setup is that I want to be able to populate the List allowing only those locations, that are not already in Table 2 (mainly because I do not want duplicate records)

Re: NEED HELP WITH QUERY

Use one of following QUery

select LocationID, LocationName from Tab1
where Tab1.CustomerID = 1 AND Tab1.LocationID not in (select LocationID from Tab2)

select LocationID, LocationName from Tab1
left outer join Tab2 on Tab1.LocationID = Tab2.LocationID
WHERE ISNULL(Tab2.LocationID,'') ='' AND Tab1.CustomerID = 1

Re: NEED HELP WITH QUERY

why dont use distinct keyword? like

Select distinct LocationID, LocationName from Table
.....

Re: NEED HELP WITH QUERY

I think Distinct will only help If I have multiple records that are the same, which I do not have in this case. I am trying to retrieve data from one table that is not already in the other table.

Example if locations were cookies.

If User1 already has a snickerdoodle and an oreo(Table 2), and there are only 3 types of cookies to choose from, then the only selection I would want in the dropdownlist is Chocolate chip(Table 1)

this is for security permissions to edit data for users and location info, but only at certain locations. So if a client has 10 locations, but you only want the manager of office a to be able to update info for people at office a, and not b,c,d... but if you have a district manager who is in charge of 5 offices, then you would want him to be able to edit data for those 5 offices. Now if you give him 10 offices, and he is already in charge of 5, then I do not want the person to be able to select the 5 he already has since this would cause duplicate records. In that case I could use distinct, but my database could be full of duplicate records which would not be data friendly if you have 1000 clients and 10,000 records but only really use 4000 because the rest are duplicate.

Re: NEED HELP WITH QUERY

Hi Bobby,

Have you tried @Anil Gaikwad's method? It seems the query will work fine with your data.

If your problem still here, please tell us as free.

Thanks.