data pull from dissimilar tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

data pull from dissimilar tables

Author

Topic

stillme67
Starting Member

4 Posts

Posted - 2007-12-11 : 16:28:10

i am completely new to sql but have a need to create a sort of check registry.
the data comes from two separate files like

checks
date num name acct amount
09/17/07 1747 companyA tmz1 87.50
09/18/07 1748 companyB tmz1 99.24
09/18/07 9326 company1 tmz2 9.65
09/18/07 1749 companyC tmz1 103.54
09/20/07 9327 company2 tmz2 55.01
09/20/07 1750 companyD tmz1 87.12

deposits
date type acct amount
09/17/07 deposit tmz1 100.00
09/17/07 deposit tmz2 200.00
09/19/07 deposit tmz1 300.00
09/19/07 deposit tmz2 400.00

i would like something like this as a result

(1st group - tmz1)
09/17/07 1747 companyA tmz1 87.50
09/17/07 deposit tmz1 100.00
09/18/07 1748 companyB tmz1 99.24
09/18/07 1749 companyC tmz1 103.54
09/19/07 deposit tmz1 300.00
09/20/07 1750 companyD tmz1 87.12

(2nd group - tmz2)
09/17/07 deposit tmz2 200.00
09/18/07 9326 company1 tmz2 9.65
09/19/07 deposit tmz2 400.00
09/20/07 9327 company2 tmz2 55.01

but when i form my statement with an inner join, i miss records, with the outer,
i get a ton of records (more than both tables combined).
i have been working with sql for about week now and can do simple queries but this is
beyond me at this point. any help what so ever would be greatly appreciated.

stillme67
Starting Member

4 Posts

Posted - 2007-12-12 : 19:39:09

no one? can this not be done? am i inquirying in the wrong area?
or do i need to explain this better?

any help would be awesome... thanks

Bill

singularity
Posting Yak Master

153 Posts

Posted - 2007-12-12 : 19:59:36

I think all you need to do is union the two tables together. No joins necessary.

select date, num, name, NULL as type, acct, amount from checks
union
select date, NULL as num, NULL as name, type, acct amount from deposits

If you need to split the result set into separate tables based on the account name, you can do:

select date, num, name, NULL as type, acct, amount from checks
where acct = tmz1
union
select date, NULL as num, NULL as name, type, acct amount from deposits
where acct = tmz1

stillme67
Starting Member

4 Posts

Posted - 2007-12-12 : 21:56:35

hey thank you much ... i will try it out!

stillme67
Starting Member

4 Posts

Posted - 2007-12-19 : 16:46:04

this is my resulting sql. both parts work fine separately but fails once i put it together with the union statement.

select
t0.company_id t0company_id,
t0.bank_account_id t0bank_account_id,
t0.check_number t0check_number,
t0.check_date t0check_date,
t0.payee_name t0payee_name,
t0.amount t0amount,
t0.clear_date t0clear_date,
t0.void_date t0void_date,

NULL AS t0bank_trx_code_id,

't0amountsub' =
case
when t0.void_date > 01/01/1990 then 0
when t0.void_date < 01/01/1900 then t0.amount
else t0.amount
end,
't0comp_name' =
case
when t0.company_id = 'TMS' then 'Van'
when t0.company_id = 'TMS2' then 'Tanker'
when t0.company_id = 'TMS3' then 'TMS3'
when t0.company_id = 'TMS4' then 'TMS4'
when t0.company_id = 'TMS5' then 'TMS5'
end
from ap_check t0
where t0.check_date >= 12/01/2007
order by t0.company_id ASC, t0.check_number ASC

union

select
t1.company_id t0company_id,
t1.bank_account_id t0bank_account_id,
t1.amount t0amount,
t1.transaction_date t0check_date,
t1.bank_trx_code_id t0bank_trx_code_id,

NULL AS t0check_number,
NULL AS t0payee_name,
NULL AS t0clear_date,
NULL AS t0void_date,
NULL AS t0amountsub,

't0comp_name' =
case
when t1.company_id = 'TMS' then 'Van'
when t1.company_id = 'TMS2' then 'Tanker'
when t1.company_id = 'TMS3' then 'TMS3'
when t1.company_id = 'TMS4' then 'TMS4'
when t1.company_id = 'TMS5' then 'TMS5'
end
from bank_rec_trx t1
where t1.bank_trx_code_id = 'DEP' and t1.transaction_date >= 12/01/2007
order by t1.company_id ASC

could this be due to my use of aliases? or possibly the use of the same alias for two different fields?
ie t1.transaction_date t0check_date (2nd table) & t0.check_date t0check_date (1st table)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 17:06:59

[code]DECLARE @Checks TABLE (Date DATETIME, Num INT, Name VARCHAR(10), Acct VARCHAR(10), Amount MONEY)

SET DATEFORMAT MDY

INSERT @Checks
SELECT '09/17/07', 1747, 'companyA', 'tmz1', 87.50 UNION ALL
SELECT '09/18/07', 1748, 'companyB', 'tmz1', 99.24 UNION ALL
SELECT '09/18/07', 9326, 'company1', 'tmz2', 9.65 UNION ALL
SELECT '09/18/07', 1749, 'companyC', 'tmz1', 103.54 UNION ALL
SELECT '09/20/07', 9327, 'company2', 'tmz2', 55.01 UNION ALL
SELECT '09/20/07', 1750, 'companyD', 'tmz1', 87.12

DECLARE @Deposits TABLE (Date DATETIME, Type VARCHAR(10), Acct VARCHAR(10), Amount MONEY)

INSERT @Deposits
SELECT '09/17/07', 'deposit', 'tmz1', 100.00 UNION ALL
SELECT '09/17/07', 'deposit', 'tmz2', 200.00 UNION ALL
SELECT '09/19/07', 'deposit', 'tmz1', 300.00 UNION ALL
SELECT '09/19/07', 'deposit', 'tmz2', 400.00

SELECT Acct,
Date,
Num,
Type,
Name,
Amount
FROM (
SELECT Acct,
Date,
Num,
NULL AS Type,
Name,
Amount,
0 AS SortOrder
FROM @Checks

UNION ALL

SELECT Acct,
Date,
NULL,
Type,
NULL,
Amount,
1
FROM @Deposits
) AS d
ORDER BY Acct,
Date,
SortOrder,
Num[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources