how to do a join after doing a union between 2 tables

[rrmcguire]

We have 2 tables, gl_ledger and gl_ledger_hist which we have done a UNION ALL with as

select * from gl_ledger UNION ALL select * from gl_ledger_hist

and it brings in our data from this, but we also need to join the data together with a third table, gl_account which has a different of columns

all three tables should be able to be joined by the field 'id'

thanks for any help provided

 

[gmmastros]

The basic structure would be...

Select *
From   (
       Select * From gl_ledger

       Union All

       Select * From gl_ledger_hist
       ) As gl
       Inner Join AnotherTable
         On gl.id = AnotherTable.id

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rrmcguire]

what do you mean Another table? What we're trying to do is do a union between gl_ledger and gl_ledger_hist then from that do a join with gl_account

thanks for any help provided

 

[rrmcguire]

ok, I think I got it...

Select *
From (
Select * From gl_ledger

Union All

Select * From gl_ledger_hist
) As gl
Inner Join gl_account
On gl.id = gl_account.id


where another table is gl_account, so we run the query but if the length of gl.id does not equal gl_account.id will the query not work?

thank you

 

[gmmastros]

I don't understand your question. Why would the length be different?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rrmcguire]

the length of the id in table the tables for gl_ledger and gl_ledger_hist is not the same as the length of the id of the id in gl_account

the one in gl_account is char(20) where as in gl_ledger and gl_ledger_hit is char(32)

 

[johnherman]

Use the TSQL function CAST or CONVERT to change all those ACCOUNT ID to numeric. Then do the join. If you can't do that because some are CHAR, then you will need to either LTRIM the longer ACCOUNT ID or CONCAT on the left side of the shorter ACCOUNT ID, whichever is easier or makes more sense, so that they are the same length.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[rrmcguire]

thanks for the reply John....can this be done within the Query?

 

[gmmastros]

If you're referring to the join condition: On gl.id = gl_account.id

When sql server compares values of differing data types, it will automatically convert one of them to match the other one. The rules for data type precedence can be found here:

https://msdn.microsoft.com/en-us/library/ms190309.aspx

Basically, if you try to compare a char to a varchar of differing lengths, sql server will convert the char to a varchar (for the purposes of the comparison). If the lengths are different, the shorter string will be converted to the longer string. Therefore, comparing char(20) and varchar(32) will cause sql server to convert (for the purposes of the comparison) to varchar(32).



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[rrmcguire]

they are both char data types

the one in gl_account is char(20) where as in gl_ledger and gl_ledger_hist is char(32)


When I run the above query it is not working so I was assuming it was because of the differing char lengths on the id's?


Select *
From (
Select * From gl_ledger

Union All

Select * From gl_ledger_hist
) As gl
Inner Join gl_account
On gl.id = gl_account.id

 

[johnherman]

While you can use George's suggestion, I prefer to be more specific. What is the difference between the two key fields? (besides the length) Is the significant data left justified with trailing spaces? Is the data right justified with leading zeros or spaces? And you never answered my question as to whether these Account ID's are actually numeric or if they have non-numeric characters. Those and other related questions are necessary to insure a correctly specified join. Or, you can use George's method which may or may not work depending on the questions I asked.

==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than cognitive innovative agile big data clouds)

 

[rrmcguire]

I believe this morning I may have gotten it

Select *
From (
Select * From gl_ledger

Union All

Select * From gl_ledger_hist
) As gl
INNER JOIN gl_account
On gl.glid = gl_account.id



On what you had suggested for the query I looked into the tables and for the 2 unioned together they are using glid which then should be joined with the id on table gl_account.

thanks for all your help on this

 

[rrmcguire]

ok, I spoke too soon now when I run my query its giving this:


Column with source name 'company_id' and LookupTableID '' already exists in this DataSet


company_id and LookupTableID I believe exist in all three tables

 

[gmmastros]

Try replacing the *'s with actual column names.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[fredericofonseca]

the issue you have is as you stated that the 2 columns mentioned exist on all 3 table so when you load the final result set into a object it will have 2 columns names company_id and 2 names lookuptableid which is not valid.
you need to replace the *'s with the column names and then either not select the second duplicated column or give them another name.

e.g.

Select gl.company_id 
     , gl.LookupTableID
     , gla.company_id as gla_company_id -- or remove this column if not required
     , gla.LookupTableID as gla_LookupTableID -- or remove this column if not required
From (
Select company_id 
     , LookupTableID
   --... all columns
From gl_ledger

Union All

Select company_id 
     , LookupTableID
   --... all columns
From gl_ledger_hist
) As gl
INNER JOIN gl_account gla
On gl.glid = gla.id

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[rrmcguire]

not too many fields to write in, so we did that and all is good...thank you both for your help

 

[rrmcguire]

so, the query uploaded our data as we're using a platform called Domo to then create charts/graphs from the data, but didn't bring in any of the fields on the UNION ALL between gl_ledger and gl_ledger_hist

 

[philhege]

Sounds like a question for a Domo forum.

-----------
With business clients like mine, you'd be better off herding cats.