Dynamic Query

Question

Dynamic Query

Leighton21

Mr or Mrs. 500

Points: 557
More actions
June 28, 2016 at 5:04 pm

#308741

Hi All,
Is there a quick way to dynamically join all related tables in a schema (based on their foreign key relationships) to produce a single row per record (it doesn't need to pivot at this stage). I was wondering if it were possible with a recursive CTE or something. I am after something dynamic in case a field changes or a table is added. And therefore wouldn't have to modify the query. I have seen some examples however they don't order the joins correctly say if you have a table that is greater than 2 in a join hierarchy i.e. Table1 (central table) joins to Table2 and Table2 joins to Table3 (table 3 joins to no other table)
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's possible, but it's not easy.

An option, if you want to keep queries dynamic is to use an ORM such as Entity framework. Be aware that if it's not handled correctly it will lead to performance problems.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

What's the goal here? To join everything to everything and bring back all the data? To what purpose?

Also, you say "single row per record". What do you mean by this?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Leighton21 Mr or Mrs. 500 Points: 557 More actions · Answer 3

Hi

Sorry, the purpose is to export to a 3rd party which only accepts csv or tab delimited data (not particularly cool) so the external system is expecting each record that spans multiple tables in my db to be exported as a flat file.

I could simply hard code the query joining all the necessary tables but if a new table is added anytime in the future then I was hoping to not have to modify the query

Thanks

aaron.reese SSChampion Points: 13498 More actions · Answer 4

so if a customer has 10 orders and each order has 10 lines, you would expect to see the customer details 100 times. how about if the customer has two phone numbers, would you now expect to see 200 lines: The 1:N relationship between customer and phone numbers has nothing to do with the 1:N customer-order relationship or the 1:n Order-Lines relationship.

This sounds like the wrong solution to the wrong problem.