3

Is there a way to join between SQL tables dynamically? For example, a function that looks at the tables provided and generate join statements between primary and foreign keys

I have items in winform listview as 

 1. TABLE1.NAME
 1. TABLE2.AGE
 1. TABLE3.ADDRESS

In the database, I have 3 tables: 

TABLE1
------
 - TBL1_ID 
 - NAME

TABLE2
------
 - TBL_ID2
 - TBL1_ID_FK
 - AGE

TABLE3
------
 - TBL_ID3
 - TBL2_ID_FK
 - ADDRESS

The output that I am trying to achieve should look like this:

SELECT TABLE1.NAME, TABLE2.AGE, TABLE3.ADDRESS FROM TABLE1 JOIN TABLE2 ON TBL1_ID_FK = TBL1_ID JOIN TABLE3 ON TBL2_ID_FK = TBL_ID2

jdidi
  • 159
  • 1
  • 4
  • 15
  • 3
    Which RDBMS is this for? Please add a tag to specify whether you're using `mysql`, `postgresql`, `sql-server`, `oracle` or `db2` - or something else entirely. – marc_s Aug 25 '16 at 13:49
  • 1
    It will be very ugly if you do this in SQL – Pரதீப் Aug 25 '16 at 13:50
  • Wouldn't this just be a question of generating the appropriate SQL? Are you asking for an algorithm here or something else? – Rune Grimstad Aug 25 '16 at 13:52
  • it could be achieved through SQL but most work will be carried on c# winform. I prefer if its a c# function, that communicate with the database and generate the SELECT statement – jdidi Aug 25 '16 at 14:43
  • If you just have three tables, why not just set up the joins (left outer if necessary) and only show the fields that are selected? – Paddy Sep 02 '16 at 09:45
  • I am upvoting your question so your question will get more attention from the user. Make sure to keep editing it for more attention. Hope you get the answer here – Neeraj Kumar Sep 02 '16 at 09:57
  • @Paddy the purpose of this work is to create a select statement no matter how many tables or columns. For example, if user added to the listview [table1].[column1] then I'd expect a function to spit: "SELECT table1.column1 FROM table1". if user added to the same listview [table2].[Col5] then I'd expect the function to find the foreign key between table1 and table2 and spit for example: SELECT table1.column1,[table2].[Col5] FROM table1 join table2 on table1 .pk_table1 = table2 .fk_table1 – jdidi Sep 02 '16 at 10:07

3 Answers3

1

you probably don't want to do this... if you REALLY think you need to do this see this as an idea of an approach:

read and understand the TSQL SELECT syntax: https://msdn.microsoft.com/en-US/en-en/library/ms189499.aspx

break down your problem into the fragments of that syntax

you need to create a select_list, a table_source and most likely a WHERE clause

create a datastructure that represents your schema (how your tables are to be joined, and which columns are in which table)

for a column selection, itterate through all selected columns, and find the tables they reside in. store the table selection in a temporary list (keep in mind that a column selection also needs to hold information about the association to the other selected columns ... for example, a Person can be someone who is a buyer of some goods, or the seller, while in both cases, the records are stored in the same table. the columns alone dont hold enough information about which way to join the Person table ... on the buyer or the seller side?)

for the table selection, select one of the tables as the starting point and start with this table as the first table of your FROM clause, itterate through all selected tables, and find ALL paths along your schema definition on how to connect/join them and keep those that are valid for the associations of your columnselection. walk along the paths and add all tables with their associated join condition to the from clause, while giving each a distinct name. optionally now reduce the joins based on the tables and associations (you need only one pair for each)

update the columselection to reference the names given to the joined tables, use the association information of the table to match the right columns

in the end, actually generate text for all fragments, and put them together in the right order...

as you can see a GENERAL solution is hard work ... if you can water down your expectations, especially on the association side, you can greatly reduce the complexity, but it will not work for every situation

or use an ORM which will save you litterally thousands of hours...

wit EF for example, you can use a user defined expression tree to project the result you want to have, and EF takes care of the statement for you

DarkSquirrel42
  • 10,167
  • 3
  • 20
  • 31
  • should this be accomplished in SQL to C#? – jdidi Sep 02 '16 at 10:52
  • it is roughly the way to go for a general approach to programatically crreate the statement ... as you can see that is a lot of complexity ... are you sure you can not use a ORM like EF? – DarkSquirrel42 Sep 02 '16 at 12:16
  • I can use anything as long as I get the output statement, can you advise where to start about using EF – jdidi Sep 02 '16 at 12:51
  • 1
    i'd start here: https://msdn.microsoft.com/en-US/en-en/data/ef.aspx ... and as a note, you can let EF print the statement ... http://stackoverflow.com/questions/1412863/how-do-i-view-the-sql-generated-by-the-entity-framework – DarkSquirrel42 Sep 02 '16 at 15:51
0

Just to name a few:

LINQ to SQL: https://msdn.microsoft.com/en-us/library/bb425822.aspx

Entity Framework: http://www.entityframeworktutorial.net/what-is-entityframework.aspx

Im using LINQ to SQL and i'm happy with it. So i would recommend it if you want to code instead of "designing" things.

ViRuSTriNiTy
  • 5,017
  • 2
  • 32
  • 58
  • in addition to this a reader might find the approach here usefull: http://stackoverflow.com/questions/27413290/how-to-create-a-dynamic-linq-select-projection-function-from-a-string-of-names – DarkSquirrel42 Sep 02 '16 at 09:47
  • @DarkSquirrel42 I'm looking for a function that generates the statement not the output – jdidi Sep 02 '16 at 09:56
0

Although I consider this a bad practice, sometimes you have to create the SQL statement dynamically. In this case you create the command as a string and then run it using the SQL's EXEC command.

Here is an example (strictly SQL statements for SQL Server):

declare @cmd varchar(max) = 'Select * from Name'
-- apply your logic to modify the select statement and create the final query
EXEC (@cmd)
Sparrow
  • 2,548
  • 1
  • 24
  • 28