datagrid and multiple datarelations 1

[hayt]

I have been searching all day for a solution, and cannot find an answer. I have a dataset, with 16 tables. The tables are all linked in some fasion (thorugh other tables). I am trying to display data from one table in specific (lets call it tableA). tableA has 5 lookup tables and 7 foreign keys. I want my datagrid to show the columns in tableA, but display the lookup value for the 7 foreign key columns. I cannot have my users clicking the datagrid to "expand" to the child tables, and since there are 5 tables, it would only show one anyway. i have datarelations set up for every foreign key in all of my tables.

Please help me, i will name my first born after you.

thanks,

hayt

 

[jebenson]

Don't try to do this with DataRelations, because it won't work the way you want. You should create the DataTable you want in SQL, pulling the lookup values in place of the tableA columns via JOINs. Then, just display the DataTable in your grid.

Post back if you need any help with SQL, etc.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[hayt]

I was afraid of that. I am trying to write the SQL now, and i have to admit it is a bit difficult. i HATE joins, and therefore have always defined my joins in the where clause (ie - where table2.foreignkey1 = table1.primarykey1). But my problem with that is that i have three foreign keys that all reference the same table (it is an employee table). any ideas???

 

[hayt]

i am using unique table aliases for each instance of the employee table, but the quesry is prompting me for a value for one of the employee fields. what gives?

 

[jebenson]

That really shouldn't be a problem. However, this is not the forum for SQL questions. You should post your question in the ASNI_SQL forum at this link: forum220

A good method I have used to construct complex joins is to take it one join at a time. Go into a program where you can write and execute SQL statements (such as Query Analyzer for SQL Server) and write a query with just one join. Once you get that working correctly, add the next join, and so forth until you have the data you want.

Also, don't think that you have to use ONLY joins. You may be better served for some parts of the query to use subselects, etc. Keep an open mind.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[hayt]

I got it. thanks. I am a bit disappointed that .net can't support tjis feature with a relation dbms. seems liek something that datasets SHOULD do.

 

[jebenson]

Well, the way I look at it is if the dataset did do this, it would probably get it wrong more often than it would get it right.


I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

 

[ca8msm]

And it would be a lot slower...


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ca8msm]

...and more memory intensive...


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.