Relating 2 typed lists

Posts   
 
    
Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 07-Feb-2006 19:38:00   

Hello

I need to construct a fairly complicted query. I know I can do this by using a typed view, but I would like to know if I can do what I would like using typedlists. What I would like to is mimic a query that selects from another typed list. Lett me explain: In SQL Server I have saved a view. In another view I added a table and then added the saved view, then added a 1:m relation between the primary key and the corresponding field in one of the tables in the typed view. The SQL looks something like this:

SELECT dbo.Property_Bookings.PB_RefId, dbo.RSS_CustomerView.Customer_FirstName, dbo.RSS_CustomerView.Customer_LastName, dbo.RSS_CustomerView.CustomerCountry FROM dbo.Property_Bookings INNER JOIN dbo.RSS_CustomerView ON dbo.Property_Bookings.CustomerId = dbo.RSS_CustomersView.CustomerId

The table is "Property_Bookings" and the view is "RSS_CustomerView". The view "RSS_CustomersView" has 2 related tables: "Customers" and "Countries"

So - here is my question: If I have a typed list called "PropertyBookings" and another typed list called "Customers" (this typed list has the same 2 entities mentioned above), is there a way to relate the two typed lists to created one recordset like the above SQL query would? I really don't like the idea of relying on views in our SQL server - I would rather do it in code..

TIA

Steve

bclubb
User
Posts: 934
Joined: 12-Feb-2004
# Posted on: 08-Feb-2006 03:26:06   

Why wouldn't you be able to just join property_bookngs to customer and then customer to countries? And take care of all of this in one typedlist? Maybe a little more information about that join could shed some light on your difficulties.

Stoop
User
Posts: 66
Joined: 28-Feb-2004
# Posted on: 08-Feb-2006 09:05:47   

Hi

Maybe I didn't explain clearly what I need to try and do. Let's try this example:

I have 3 tables:

ApartmentReservations Customers with a 1:m relation to "ApartmentReservations" in CustomerId Countries (2 fields: PrimaryKey "CountryCode" and string field called "CountryName")

Both "ApartmentReservations" and "Customers" have a field called "CountryCode " that have a m:1 relationship with "Countries"

I need to create a typed list that gives me the "CountryName" for both "Customer" and "ApartmentReservations" which obviously could be different

Example record*

CustomerLastName ApartmentName CustomerCountry* ApartmentCountry*


Jones Hilton France Germany

*Aliases for "CountryName"

I hope this is a bit more clear on what I need to do..

Steve

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 08-Feb-2006 10:32:09   

You can add Countries twice, and alias it so you recognize which 'country' table you're talking about simple_smile . You do that in the first tab of the typedlist editor. Consult the relation shown on the tab as well to see which country relation you're dealing with.

Then in the fields tab, add the fields you want and in the grid which shows the fields added to the typed list, you can change the names to new ones so you get the proper aliases like you described.

Please let us know if you run into problems with this.

Frans Bouma | Lead developer LLBLGen Pro