asked on

LINQ to SQL or SQL Select Multiple from Foriegn Table

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)
End If
End If
Next

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

BizID=1
ValueID=4

BizID=1
ValueID=7

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.

Mlanda T

Try this for building the SQL:

            dim criteria as string =  string.empty

            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
                        criteria &= iif(criteria=string.empty, "", " OR ") & " x.valueid=" & ddl.SelectedValue)
                    End If
                End If
            Next
 
            if criteria <> string.empty then strsQL.Append(" AND (" & criteria & ")")
            strSQL.Append(" Order By b.BizName")

Open in new window

flosoft

ASKER

That did not work, I will attach an image to give a better idea...

These are the 3 relevant tables...
Business, Properties and xTable.

I load drop downs with properies (properties have a type id which defines how many dropdowns there may or may not be in the loop of controls).

So lets say for example I have 3 restaurants in my business table.
There are 3 different property types (thus 3 dropdowns). of cuisine, city and rating....

What I want to do is get all restaurants that would match the properties selected in the dropdowns.

Remember I am creating the dropdowns dynamically based on how many property types there are, above is just an example.

Image2.jpg

Nasir Razzaq

May be I am bit sleepy but can you not do

"Select ... From ... Where BizID=" & dropdown1.Text & " AND ValidID=" & dropdown2.Text

flosoft

ASKER

No,
The drop downs contain PropID values that sit in the xTable in the image I uploaded.

Nasir Razzaq

So? You select a businessID from one drop down and you select a propertyID from the other dropdown and you want to load all records from xTable that match the two IDs?

flosoft

ASKER

No no business is seleted at all.

I want all businesses that match the properties in the xtable, but I need it to work like this.

I will use a restaurant as an example.

Lets say I have restaurtant 1 with an ID of 1and restaurant 2 with an ID of 2.

Restaurant 1 has properties 5 and 7
Restaurant 2 has properties 5 and 9

A user selects property 5, they see both. they select 5 and 7 they see Restaurant 1 only, they select 5 and 9 they see Restaurant 2 only.

Nasir Razzaq

Are both 5 and 7 in the same column?

>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.

Burger and Casual can not be in same column right?

If one dropdown is FoodTypes and other is Atmosphere and the user may select either one or both, this code may work

Dim Filter As String
If String.IsNullOrEmpty(ddl1.Text) = False Then
Filter = "columnname1 = " & ddl1.SelectedValue
End If
If String.IsNullOrEmpty(ddl2.Text) = False Then
If String.IsNullOrEmpty(Filter) = False Then
Filter &= "AND columnname2=" & ddl2.SelectedValue
Else
Filter = "columnname2=" & ddl2.SelectedValue
End If

flosoft

ASKER

I am attaching another image.

The db is setup like this...

There is an Entity Table (Business).
EntityType Table - (Type of Business).
ListEntityProperties (Properties that can belong to a business type).
Property Values (Values that can belong to a property).

So in the diagram, I am creating the selection controls based on what properties the belong to the business type the user is searching for.

an Example would be a restaurant, it may contain a property of Cusine and of Location.

Cuisine may have values like Steak, Seafood, Vegan and so on and Location might be North Side, South Side and so on.

The values are put in dynamically created dropdown lists.

the xEntityPropValues table has the ID of the Entity and ID of the Property assigned to that Business.

See my previous example of what I am trying to get working...

This code will work when only 1 property is selected, but if 2 are you never get anything back...

strSQL.Append("Select e.ID, e.EntityName, x.ValueID FROM xEntityPropValues x JOIN IMEntities e ON e.ID=x.EntityID WHERE")

For Each ctl As Control In pnlOptions.Controls
If TypeOf ctl Is Telerik.Web.UI.RadComboBox Then
Dim ddl As Telerik.Web.UI.RadComboBox = ctl
If Not ddl.SelectedValue = -1 Then
strSQL.Append(" x.valueid=" & ddl.SelectedValue & " AND ")
End If
End If
Next

strSQL.Append(" e.TypeID=" & Request.Item("t"))
strSQL.Append(" Order By e.EntityName")

What that code is doing is going through all controls in the panel they were put in, checking the type. All controls have an initial item with text "select" and a value of -1 so all I am doing there is skipping that if nothing is selected.

Make sense?
Image4.jpg

Mlanda T

I think your algorithm has one small problem... you said it produces the syntax:

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

Open in new window

However, this is not correct, it will never evaluate to true.AND x.valueid=53 AND x.valueid=98 Which is where I had tried to use the OR command for you to produce something like ... AND (x.valueid=53 OR x.valueid=98)

Mlanda T

I must point out though that I have not really been able to visualise you interface from what you have explained.

flosoft

ASKER

but the OR seemed to bring in everyone for example if I had the 2 restaurants, 1 with valueid=5 and 9 and another than had valueid=5 and 7, if the user picked 5 and 9 they would still see restaurant 2.

flosoft

ASKER

you mean the ui?

Attaching an image, this is coming from a restaurant test table...
Image6.jpg

Mlanda T

what should the correct SQL look like? as in... if you had to manually write the SQL to achieve what you want... how would you approach it?

flosoft

ASKER

I am not positive, it might need grouping...

I want all records that match the selections which come from the xEntityPropValues table, but I am displaying info from the EntityTable.

Mlanda T

I think that is what you should start off with figuring out... what the final SQL should look like vis-a-vis the selections which the user is making on the interface. From what I can see so far, you are filtering records based on two fields:

xEntityPropValues.ValueID - multiple values coming from the comboboxes
IMEntities.TypeID - single value coming from a either a Cookies, Form, QueryString or ServerVariables parameter - Request.Item("t")

How do these variables relate to?

Lets say I have restaurtant 1 with an ID of 1and restaurant 2 with an ID of 2.

Restaurant 1 has properties 5 and 7
Restaurant 2 has properties 5 and 9

I honestly want to help. I just want to make sure I understand everything clearly. I hope you dont mind my asking so many questions.

ASKER CERTIFIED SOLUTION

flosoft

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

flosoft

ASKER

No real answer, I eventually wrote this query in T-SQL