We help IT Professionals succeed at work.

VB.net Create SQL Joins from a list

219 Views
Last Modified: 2014-08-22
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

PortletPaulEE Topic Advisor
CERTIFIED EXPERT
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]
CERTIFIED EXPERT

Commented:
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/VBA/VSTO Developer

Author

Commented:
Hi. No I am looking for one statement that has all three
Murray BrownASP.net/VBA/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
CERTIFIED EXPERT
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/VBA/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
CERTIFIED EXPERT
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.
CERTIFIED EXPERT

Commented:
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/VBA/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/VBA/VSTO Developer

Author

Commented:
1
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
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/VBA/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
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
EE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Murray BrownASP.net/VBA/VSTO Developer

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.