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)
"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
select LocationID, LocationName from Tab1 where Tab1.CustomerID = 1 AND Tab1.LocationID not in (select LocationID from Tab2)
OR
select LocationID, LocationName from Tab1 left outer join Tab2 on Tab1.LocationID = Tab2.LocationID WHERE ISNULL(Tab2.LocationID,'') ='' AND Tab1.CustomerID = 1
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.
"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
Bobby-Z
Contributor
2838 Points
1120 Posts
NEED HELP WITH QUERY
Apr 19, 2012 05:36 AM|LINK
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)
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
gaikwad_anil...
Contributor
2805 Points
534 Posts
Re: NEED HELP WITH QUERY
Apr 19, 2012 05:59 AM|LINK
Use one of following QUery
select LocationID, LocationName from Tab1
where Tab1.CustomerID = 1 AND Tab1.LocationID not in (select LocationID from Tab2)
OR
select LocationID, LocationName from Tab1
left outer join Tab2 on Tab1.LocationID = Tab2.LocationID
WHERE ISNULL(Tab2.LocationID,'') ='' AND Tab1.CustomerID = 1
www.thecodekey.com
Please mark as answer if useful
Mastan Oli
Contributor
5088 Points
998 Posts
Re: NEED HELP WITH QUERY
Apr 19, 2012 06:05 AM|LINK
why dont use distinct keyword? like
playingOOPS | மெய்ப்பொருள் காண்பதறிவு
Mark as Answer If you find helpful
Bobby-Z
Contributor
2838 Points
1120 Posts
Re: NEED HELP WITH QUERY
Apr 19, 2012 06:17 AM|LINK
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.
Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks
Chen Yu - MS...
All-Star
21600 Points
2493 Posts
Microsoft
Re: NEED HELP WITH QUERY
Apr 23, 2012 09:38 AM|LINK
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.
Feedback to us
Develop and promote your apps in Windows Store