CODEWITHSUNDEEP
sqlt-sql
UserSN
UserSN

Reputation: 1013

SQL query to populate dropdown field

I'm trying to query a table in my DB to populate a dropdown field on a form. I'd like Field1 as the display and Field2 as the value on my insert.

select '[CategoryName]','[CatID]' from BND_ListingCategories

The above query just populates [CategoryName] as all values in the dropdown.

Now sure what i'm doing wrong.

UPDATE:

Hey guys, So I think I understand why my query was not working I needed to add a join statement as the (CategoryName) field is on another table.

Even so with this updated query i'm now getting an error Error: Ambiguous column name 'CatID'.

select [CategoryName],[CatID] from BND_ListingCategories
inner join BND_Listing
on BND_ListingCategories.CatID=BND_Listing.CatID
where LID=1

UPDATE 2

Okay so i'm making progress pulled out the handy ol SQL for dummies.

Fixed my ambiguous problem by editing my query as such.

select c.CategoryName, l.CatID
from BND_ListingCategories AS c INNER JOIN BND_Listing as l
on c.CatID = l.CatID

Got it working just trying to see how I can SORT alphabetically by CategoryName

Upvotes: 0

Views: 1638

Answers (3)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Remove the single quotes from column names.

select [CategoryName],[CatID] from BND_ListingCategories

If you wanted to sort with category name, use ORDER BY.

   select c.CategoryName, l.CatID
   from BND_ListingCategories as c 
       INNER JOIN BND_Listing as l
           on c.CatID = l.CatID
    Order by c.CategoryName

Upvotes: 3

UserSN
UserSN

Reputation: 1013

select distinct c.CategoryName, l.CatID
from BND_ListingCategories AS c INNER JOIN BND_Listing as l
on c.CatID = l.CatID
ORDER BY CategoryName ASC

This got my dropdown working. Thanks for all your help guys. Got me looking in the right places!

Upvotes: 0

Zi0n1
Zi0n1

Reputation: 594

Your syntax is just a little off thats all. Remove the quotes and you will then return results from your query.

select [CategoryName],[CatID] from BND_ListingCategories

You should just need to modify the select list to specify the table you want to pull that column from. You may need to do it to both columns in your select statement.

Edit for new updated question: 

select [CategoryName], BND_Listing.[CatID] from BND_ListingCategories
inner join BND_Listing
on BND_ListingCategories.CatID=BND_Listing.CatID
where LID=1

Upvotes: 2

Related Questions