VB.net Create SQL Joins from a list

Murray Brown
Murray Brown used Ask the Experts™
on
Hi

I have an application where I have to convert a list of information into the FROM part of
a SQL statement. The list looks like the data below. It can be any number of lines long.
 How would I convert this?

[Table1].[Column1] Left Join [Table2].[Column6]
[Table6].[Column3] Right Join [Table2].[Column6]
[Table5].[Column1] Left Join [Table4].[Column2]
etc
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
do you mean, what would each row be in SQL?

---
[Table1].[Column1] Left Join [Table2].[Column6]

[Table1] left join [Table2] ON [Table1].[Column1] = [Table2].[Column6]

---
[Table6].[Column3] Right Join [Table2].[Column6]

[Table6] Right Join [Table2] ON [Table6].[Column3] = [Table2].[Column6]

---
[Table5].[Column1] Left Join [Table4].[Column2]

[Table5] Left Join [Table4] ON [Table5].[Column1] = [Table4].[Column2]
Expanding on PortletPaul's comment, if the list of joins is in an array of strings, you could reformat each line with code like the following:
 
        Dim strJoin(10) As String
        Dim strSQL(10) As String
        For i As Integer = 0 To strJoin.GetUpperBound(0)
            Dim dot1 As Integer = InStr(strJoin(i), ".")
            Dim bracket2 As Integer = InStr(dot1, strJoin(i), "]")
            Dim join1 As Integer = InStr(UCase(strJoin(i)), "JOIN")
            Dim table2 As Integer = InStr(join1, strJoin(i), "[")
            Dim dot2 As Integer = InStr(table2, strJoin(i), ".")
            strSQL(i) = Mid(strJoin(i), 1, dot1 - 1) & Mid(strJoin(i), bracket2 + 1, table2 - bracket2 - 1) & Mid(strJoin(i), table2, dot2 - table2) _
                & " ON " & Mid(strJoin(i), 1, bracket2) & " = " & Mid(strJoin(i), table2)
        Next

Open in new window

If the lines are coming from another, then adjust the reference to strJoin(i) appropriately. If you need help with that, please specify where the input text is coming from.
Murray BrownASP.net/VSTO Developer

Author

Commented:
Hi. No I am looking for one statement that has all three
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Murray BrownASP.net/VSTO Developer

Author

Commented:
Essentially I am trying to build a VB.net function that builds a SQL statement from any combination of joins. An exampbple would be to return
FROM ((CLIENTS INNER JOIN JOBS ON CLIENTS.[CLIENT ID] = JOBS.[CLIENT ID]) INNER JOIN CONTACTS ON JOBS.CATEGORY = CONTACTS.CONTACT) INNER JOIN MATERIALS ON (MATERIALS.[MATERIAL DETAIL] = CONTACTS.LANDLINE_NO) AND (CLIENTS.[CLIENT NAME] = MATERIALS.[MATERIALS ID])

from the joins shown in the image below:

1
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Your example is using Access syntax, is the db Access or mssql?

Note the absence of parentheses in the following (for mssql):
SELECT
      *
FROM CLIENTS
      INNER JOIN JOBS
                  ON CLIENTS.[CLIENT ID] = JOBS.[CLIENT ID]
      INNER JOIN CONTACTS
                  ON JOBS.CATEGORY = CONTACTS.CONTACT
      INNER JOIN MATERIALS
                  ON MATERIALS.[MATERIAL DETAIL] = CONTACTS.LANDLINE_NO
                        AND CLIENTS.[CLIENT NAME] = MATERIALS.[MATERIALS ID]
;

Open in new window

Murray BrownASP.net/VSTO Developer

Author

Commented:
The DB is SQL. I really just want to know what syntax I could use to create unlimited joins
Surely the following syntax showing one line per join would worj
FROM
CLIENTS INNER JOIN JOBS ON CLIENTS.[CLIENT ID] = JOBS.[CLIENT ID],
JOBS INNER JOIN CONTACTS ON JOBS.CATEGORY = CONTACTS.CONTACT,
etc
etc
etc
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"I really just want to know what syntax I could use to create unlimited joins"

Well the syntax is well established and documented. see FROM clause in the BOL
Formatting doesn't needs to apply (indents, line breaks at all) but I suggest you try to use a minimum of parentheses. Be careful of extraneous items, for example in the comment immediately above you cannot have the trailing commas.

All I can say is I wish you good luck. You may be able to get there, but I wouldn't even try.
Why do you want to have this function? What's the actual source of the join information? It's pretty uncommon to have random groupings of tables that wouldn't be known at design time. If you're trying to make development easier, I'm not sure that the time spent developing the function will be worth it. If you're going to allow the user to type in a join, then you've got all sorts of potential problems to guard against. If you're working from internal building blocks, you could structure the building blocks to be much easier to work with.
Murray BrownASP.net/VSTO Developer

Author

Commented:
So from what you are both saying it is not worthwhile trying to build the GUI below that allows a user to drag and drop columns into the DataGridView that the code then builds your joins from. This is a pity as it is absolutely vital in my app.
Murray BrownASP.net/VSTO Developer

Author

Commented:
1
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
The image above suggests you will hold meta-data about each of those objects (e.g. that FOB is a numeric) and presumably you will know what are the keys and foreign keys too. Up till now I had considered you were dealing with a list of (dumb) strings

"[Table1].[Column1] Left Join [Table2].[Column6]"
"[Table6].[Column3] Right Join [Table2].[Column6]"
"[Table5].[Column1] Left Join [Table4].[Column2]"

But it won't be like that - I hope. Have you designed the data model for your meta data?
Murray BrownASP.net/VSTO Developer

Author

Commented:
Hi. Yes I have used meta data as in data types. I could just as easily find key and foreign key information. I should have posted that image initially so sorry about that
I'm sorry, I won't be able to help more on this. I'm going to be unavailable for a week....
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
What you are aiming at is ambitious and I don't think I can add much either. To me it's your responsibility - through the meta data - to know what can be joined to what. It isn't a free-for-all/melee/mosh-pit; there has to be some rules applied.

The syntax for joining isn't that complex, it is well documented. Refer to that documentation if in doubt.
What you allow, and how you allow it, is in your hands.
Murray BrownASP.net/VSTO Developer

Author

Commented:
Thank you both for all of the time you have taken out to help me. Very much appreciated

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start Today