Basic Table join, very confusing

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

SQL Server 2008 Forums

Transact-SQL (2008)

Basic Table join, very confusing

Author

Topic

in_beta
Starting Member

2 Posts

Posted - 2011-09-08 : 00:39:18

Hi There,

I have been playing around with a specific query that I just cannot get my head around.

I have 2 separate queries that work perfectly, but when I need to merge the data together, or join rather, either my code is wrong or I am just not using the correct method.

The first query is as follows:


declare @one int , @two int
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))


 SET @two = (select sum(DocTotal) as AP2 From orin where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
 
 select @one 'debit',
        @two 'credit',
        (@one-@two)/1.1

This essentially gets me the desired result for a mathematical equation.

The second query is as follows:


select CardCode, CardName, Phone1, Cellular from ocrd where groupcode='102' and SlpCode='66' order by CardName

As mentioned previously, these work when run by themselves.

The question I have is how to merge the data together.

The columns should be like this:

CardCode CardName Phone1 Cellular (@one-@two)/1.1

Hopefully this makes sense, if you need any more information please let me know.

I'm really hitting a brick wall with this one.

Thanks!

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-09-08 : 01:59:47

Is there any relationship between the 3 tables?

in_beta
Starting Member

2 Posts

Posted - 2011-09-08 : 02:34:56

Yes there is, CardCode would be the perfect match.

I have had an answer on another forum as follows:

declare @one int , @two int 
SET @one = (Select sum(DocTotal) as AP1 From oinv where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
SET @two = (select sum(DocTotal) as AP2 From orin where (DocDate >= '2010-01-01 00:00:00.000' and DocDate <= '2010-12-31 23:59:59.000' and CardCode = 'c1003'))
 

select t1.CardCode, t1.CardName, t1.Phone1, t1.Cellular, t2.three 
from ocrd as t1
inner join ( select (@one-@two)/1.1 as three
                        ) as t2
on t1.cardcode <> '' --need to put a clause will be always true here                     
        

where t1.groupcode='102' and t1.SlpCode='66' 
order by t1.CardName

This works well although each row returns the value of CardCode c1003. I need to know how to change that to a variable I suppose?

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources