Visual basic SQL server Version 3.0 December 20th, 2010 SelfServing

I am having a hard time duplicating the following query:

SELECT PBStoreLocations.locDescription, Vendor.VName, DefaultFuel.StoreID, DefaultFuel.Vendor FROM (DefaultFuel INNER JOIN Vendor ON DefaultFuel.Vendor = Vendor.VCorpVID) INNER JOIN PBStoreLocations ON DefaultFuel.StoreID = PBStoreLocations.locStore GROUP BY PBStoreLocations.locDescription, Vendor.VName, DefaultFuel.StoreID, DefaultFuel.Vendor;

There are no relations defined at all. Actually the PBStoreLoacations table and the Vendor table are in one database and the DefaultFuel table is in a different one.

DefaultFuel layout is: Vendor layout is: PBStoreLocation layout is: StoreID VCorpVid LocStore Vendor VName LocDescription . . . . . . . . . . . . etc. etc. etc.

I am going to use the results in a datagrid (read only).

I have looked at the derived tables and dynamic relations document and I am having a hard time with it.

Any help in code would be greatly appreciated.

Could you create this a view in one of the database, and then create a TypedView in the designer to map to it - this can often be easier with cross database stuff that trying to create it in code ?

Failing that, can you show us what you have tried so far, and what went wrong with it...?

Matt

You have all catalogs/schemas in your query in the project? You can define relationships in the model without having them in the database.

You can in code also use either Linq to join entities or using the DynamicRelation class to create relations on the fly in code to join between entities.

Sorry it has taken me so long to respond, I was out for a couple of weeks.

The PBStoreLocations and the Vendor tables are in one LLBL project and the DefaultFuel table are in a seperate LLBL project.

Maybe this is wrong???

When the project was started a seperate LLBL project was created for each SQL database(catalog). Instead of one LLBL project containing all of the databases(catalogs). Should the LLBL project include all of the databases? I assume this would allow me to add relationships as needed. If I create one LLBL project with all databases would it be best to group them by database and create seperate projects for them? How to do the connection sting?

I do not know why things were done the way they were done (before my time), I guess I am looking for the best way to set things up. If I have to redo everything, so be it.

Thanks

Both of the suggested solutions are valid, you can really only decide for yourself which one is better for your situation.

ConnectionStrings do not neccesarily need to contain the database name - this is actually generated into the persistence info, and can be overwritten as needed. As long as the databases are on the same server you can use the same connection string for both.

Matt