[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VB.net Create SQL Joins from a list

Posted on 2014-08-16
15
Medium Priority
?
198 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
0
Comment
Question by:Murray Brown
  • 7
  • 5
  • 3
15 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40265976
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]
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 40271771
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.
0
 

Author Comment

by:Murray Brown
ID: 40271883
Hi. No I am looking for one statement that has all three
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Murray Brown
ID: 40272127
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40272139
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

0
 

Author Comment

by:Murray Brown
ID: 40272337
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40272546
>>"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.
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 40273222
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.
0
 

Author Comment

by:Murray Brown
ID: 40275473
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.
0
 

Author Comment

by:Murray Brown
ID: 40275476
1
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40275546
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?
0
 

Author Comment

by:Murray Brown
ID: 40275760
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
0
 
LVL 20

Assisted Solution

by:ElrondCT
ElrondCT earned 1000 total points
ID: 40276844
I'm sorry, I won't be able to help more on this. I'm going to be unavailable for a week....
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 1000 total points
ID: 40277797
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.
0
 

Author Closing Comment

by:Murray Brown
ID: 40279096
Thank you both for all of the time you have taken out to help me. Very much appreciated
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question