LINQ to SQL or SQL Select Multiple from Foriegn Table
Posted on 2011-04-21
I am creating a search page and dynamically adding controls to allow a user to select, so I load dropdownlists with items a user can select.
I start with this SQL
strSQL.Append("Select DISTINCT b.BizName,b.ID, x.ValueID FROM xBizPropValues x Join BIz b ON x.Bizid=b.ID WHERE b.TypeID=" & Request.Item("t"))
then I loop through the controls, find the drop down lists and get their value and append SQL...
For Each ctl As Control In pnlOptions.Controls
If TypeOf ctl Is DropDownList Then
Dim ddl As DropDownList = ctl
If Not ddl.SelectedValue = -1 Then
strSQL.Append(" AND x.valueid=" & ddl.SelectedValue)
strSQL.Append(" Order By b.BizName")
Works fine when only 1 dropdown has a selection and produces SQL like this:
Select DISTINCT b.BizName,b.ID, x.ValueID FROM xBizPropValues x Join Biz b ON x.Bizid=b.ID WHERE b.TypeID=1 AND x.valueid=53 Order By b.BizName
However if more than 1 drop down has a selection, it produces this SQL..
Select DISTINCT b.Bizname,b.ID, x.ValueID FROM xBizPropValues x Join Biz b ON x.entityid=b.ID WHERE b.TypeID=1 AND x.valueid=53 AND x.valueid=98 Order By b.EntityName
The type ID is just a type (think retailstore or restaurant etc).
The xBizPropValues table has a valueID and a BizID. BizID is linked to a business table which has the buisiness info in it and the valueID links to a table with ID and a Text Column (example might be: ID=1, Text="Shoes").
There may be be business's with many values in xBizPropValues.
So the table may have rows which look like
and so on...
I am looking for what SQL (or better yet Linq 2 SQL query) would allow this to work.
So if the business type was Restaurant for example and I have dropdowns with FoodTypes and Atmosphere I might select Burgers in dropdown 1 and Causal in Dropdown 2.
I would need the SQL to only show those that meet both criteria...
hopefully this makes sense.
Thanks for any help.