SQL multiple joins query.

[abhay_547]

Hi All,

Actually I am very bad at sql statements so I need the help on the below.
Following is the sql statement which I have currently in my excel macro. I need your help to consolidate the following code into my existing statement which is as below :

cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code FROM Data_SAP.dbo.mydata mydata INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM  ON (mydata.[Company Code] = CRM.[Company Code])INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM  ON (mydata.[Cost Center] = CCM.[Cost Center])INNER JOIN Data_SAP.dbo.[Cost Element Mapping] CEM  ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)WHERE CRM.Country IN (" & selection1 & ") AND CCM.[Sub Product UBR Code] IN (" & selection & ") AND CEM.FSI_LINE3_code IN (" & selection2 & ")AND mydata.year = '" & ComboBox4.Value & "' AND mydata.period = '" & ComboBox3.Value & "'AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'

And following is the sql statement which I want to add to the above statement :

SELECT mydata.[Value In Object Currency] *
          Fxrates.[Exchange Rate] as PriceInEuro
FROM mydata
     INNER JOIN Fxrates ON
          mydata.Period = Fxrates.Period AND
          mydata.Year = Fxrates.Year AND
          mydata.[Object Currency] = Fxrates.[Object Currency]

Thanks a lot for your help in advance.

[si_the_geek]

It is obscenely hard to read the SQL statement in your code, because you have got masses of text all on one line... which can be corrected by adding line continuation (which is valid in VB), which could be done like this::

cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code " _ 
                 & "FROM Data_SAP.dbo.mydata mydata " _ 
                 & "INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM " _ 
                 & "           ON (mydata.[Company Code] = CRM.[Company Code])" _ 
                 & "INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM " _ 
                 & "           ON (mydata.[Cost Center] = CCM.[Cost Center])" _ 
                 & "INNER JOIN Data_SAP.dbo.[Cost Element Mapping] CEM " _ 
                 & "           ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)" _ 
                 & "WHERE CRM.Country IN (" & selection1 & ") " _ 
                 & "  AND CCM.[Sub Product UBR Code] IN (" & selection & ") " _ 
                 & "  AND CEM.FSI_LINE3_code IN (" & selection2 & ")" _ 
                 & "  AND mydata.year = '" & ComboBox4.Value & "' " _ 
                 & "  AND mydata.period = '" & ComboBox3.Value & "'" _ 
                 & "  AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'

( _ after a space means the code continues on the next line, & joins two strings together)


As to your question, I have no idea what you mean by wanting to "add" two SQL statements together (there are lots of possibilities). Presumably you want some kind of merging of the result sets, but it is far from obvious how that should be.

[abhay_547]

Hi Geek,

Thanks a lot for your reply, As mentioned earlier when I say I want to add the below sql statement to my existing one then I mean that I want to include that the below statement also to my existing one that is to create one sql statement by merging both of them.

Sql Statement which I want to merge / include in my existing statement :

SELECT mydata.[Value In Object Currency] /
          Fxrates.[Exchange Rate] as Value In Euros
FROM mydata
     INNER JOIN Fxrates ON
          mydata.Period = Fxrates.Period AND
          mydata.Year = Fxrates.Year AND
          mydata.[Object Currency] = Fxrates.[Object Currency]

My existing statement :

cmd1.CommandText = "SELECT mydata.*, CRM.Country, CCM.[Sub Product UBR Code], CEM.FSI_LINE3_code, mydata.[Value In Object Currency] / Fxrates.[Exchange Rate] as Value In Euros " _ 
                 & "FROM Data_SAP.dbo.mydata mydata " _ 
                 & "INNER JOIN Data_SAP.dbo.[Country_Region Mapping] CRM " _ 
                 & "           ON (mydata.[Company Code] = CRM.[Company Code])" _ 
                 & "INNER JOIN Data_SAP.dbo.[Cost Center mapping] CCM " _ 
                 & "           ON (mydata.[Cost Center] = CCM.[Cost Center])" _ 
                 & "INNER JOIN Data_SAP.dbo.[Cost Element Mapping] CEM " _ 
                 & "           ON (mydata.[Unique Indentifier 1] = CEM.CE_SR_NO)" _               & " INNER JOIN Fxrates ON mydata.Period = Fxrates.Period AND"
          & " mydata.Year = Fxrates.Year AND mydata.[Object Currency] = Fxrates.[Object Currency]"   
                 & "WHERE CRM.Country IN (" & selection1 & ") " _ 
                 & "  AND CCM.[Sub Product UBR Code] IN (" & selection & ") " _ 
                 & "  AND CEM.FSI_LINE3_code IN (" & selection2 & ")" _ 
                 & "  AND mydata.year = '" & ComboBox4.Value & "' " _ 
                 & "  AND mydata.period = '" & ComboBox3.Value & "'" _ 
                 & "  AND mydata.[Document Type]= '" & Left(ComboBox11.Value, 2) & "'

I have tried adding the new statement into my existing sql statement. I have also highlighted those lines in Red. Please help.. I have tried to include the new statement into existing one as per my requirement.

Thanks a lot for your help in advance.

[si_the_geek]

And what is the problem with your new statement? ...or should I just spend extra time guessing what you should have told me?

Apart from minor mistakes with the line-continuations around the Inner Join, and the dodgy alias (any name with a space should be inside single quotes or square brackets, eg: ... as [Value In Euros]" ),
it looks like it will work.