• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

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.


0
flosoft
Asked:
flosoft
  • 9
  • 5
  • 3
1 Solution
 
MlandaTCommented:
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

0
 
flosoftAuthor Commented:
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
0
 
CodeCruiserCommented:
May be I am bit sleepy but can you not do

"Select ... From ... Where BizID=" & dropdown1.Text & " AND ValidID=" & dropdown2.Text
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
flosoftAuthor Commented:
No,
The drop downs contain PropID values that sit in the xTable in the image I uploaded.
0
 
CodeCruiserCommented:
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?
0
 
flosoftAuthor Commented:
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.
0
 
CodeCruiserCommented:
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
0
 
flosoftAuthor Commented:
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
0
 
MlandaTCommented:
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)
0
 
MlandaTCommented:
I must point out though that I have not really been able to visualise you interface from what you have explained.
0
 
flosoftAuthor Commented:
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.
0
 
flosoftAuthor Commented:
you mean the ui?

Attaching an image, this is coming from a restaurant test table...
Image6.jpg
0
 
MlandaTCommented:
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?
0
 
flosoftAuthor Commented:
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.
0
 
MlandaTCommented:
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.
0
 
flosoftAuthor Commented:
I don't mind at all I greatly appreciate your efforts.

If you look at my DB Diagram, you will see the ListEntityProperties and ListEntityPropertyValues Tables as well as the ListEntityTypes Table.

In these examples and Entity is a Business..

There are EntityProperties, they belong to a EntityType. Entitties also have a type.

PropertyValues belong to Properties.

On the back end of this a user can create a new property and assign it to an entity type, for example they could create a property named Hair Color, assign it to Restaurant type, they could then add values of Black, Brown, Blonde etc...
I have another screen on the back end fully working with this model where the user could then assign Black, Blonde to a particular Restaurant...which puts the entityID and the PropertyValueID into the xEntityPropValues table... all that works perfectly.


What this question is about is the front end.

If the user arrives at the page wanting to search Restaurants (which has a typeID of 1) I grab all properties that belong to Type1, create a dropdown box, populate it with the values that belong to that property. I first put in a "Select" Item into the dropdown with a value of -1 so I can skip checking that control. This is why my code is the way it is, unfortunately I am much stronger in code than SQL Strings but what I want would look like just about every other search, just I need to generate it dynamically and it is close, but just not there yet...

0
 
flosoftAuthor Commented:
No real answer, I eventually wrote this query in T-SQL
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 9
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now