VB.net Create SQL Joins from a list

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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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
ElrondCTCommented:
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Hi. No I am looking for one statement that has all three
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor 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
0
PortletPaulfreelancerCommented:
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor 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
0
PortletPaulfreelancerCommented:
>>"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
ElrondCTCommented:
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor 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.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
1
0
PortletPaulfreelancerCommented:
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
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor 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
0
ElrondCTCommented:
I'm sorry, I won't be able to help more on this. I'm going to be unavailable for a week....
0
PortletPaulfreelancerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you both for all of the time you have taken out to help me. Very much appreciated
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.