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
 jfm

Author  Topic 

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 05:48:06
Hi there,

I have two queries:

SELECT SUM (BALANCE)
FROM table_S AS S
INNER JOIN table_F AS F
ON F.ID = S.ID
WHERE CODE = 'OK'

And the other one:

SELECT SUM(BALANCE) FROM Table_S
WHERE CODE = 'OK'


Im supposed to have the same extraction as table_S contains all the info of Table_F...

In the second query Im asking for the balance in Table_S, in the first query im supposed to have the same balance as the second query. Im just connecting the tables by Id...

Any tip?

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 05:59:54
sorry didnt understand what issue is. As per your explanation provided relationship between tables is 1 to 1 you should get same result. Isnt it not observed output?

Also check if relationship is one to one between them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-04-26 : 06:01:01
Any question?

INNER JOIN will sort out rows that have no match via ID in the joined table.
So I guess that's the reason why you get different results...


Too old to Rock'n'Roll too young to die.
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 06:04:50
if I use left outer join still not working..


The matter is that in table_f i have a column that I need for table_s and the only way to connect the data is via ID. The matter is that I cant have more ID's than the ones in table_s,

But using inner join and left outer join i have much more ID's. And i don't know why
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 06:07:52
The relationship:

Table_s is provides me with the information of some Id's from table_f. Table_f is the master table and table_s is a secondary table that gives me some information but I need to group by table_s using a column from table_f. that's why im trying to cross the data

any idea?

Thank you


quote:
Originally posted by visakh16

sorry didnt understand what issue is. As per your explanation provided relationship between tables is 1 to 1 you should get same result. Isnt it not observed output?

Also check if relationship is one to one between them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-26 : 06:10:14
Did you display other columns of table_F along with SUM ?
If not,
SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F )
AND CODE = 'OK'


--
Chandu
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 06:23:35
Yes I did.

Im going back to basis as the results are not matching. I found that the information extracted by crossing the data (without adding more columns), is not matching.

SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F ) >>> please could you be more specific with this part of the query?

AND CODE = 'OK'


Thank you

quote:
Originally posted by bandi

Did you display other columns of table_F along with SUM ?
If not,
SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F )
AND CODE = 'OK'


--
Chandu

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 06:25:06
quote:
Originally posted by jfm

The relationship:

Table_s is provides me with the information of some Id's from table_f. Table_f is the master table and table_s is a secondary table that gives me some information but I need to group by table_s using a column from table_f. that's why im trying to cross the data

any idea?

Thank you


quote:
Originally posted by visakh16

sorry didnt understand what issue is. As per your explanation provided relationship between tables is 1 to 1 you should get same result. Isnt it not observed output?

Also check if relationship is one to one between them.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs





use derived tables like

SELECT f.*,Total
FROM table_F f
INNER JOIN (SELECT ID, SUM(BALANCE) AS Total
FROM table_S
GROUP BY ID
)s
On f.ID = s.ID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-26 : 06:34:11
quote:
Originally posted by jfm

Yes I did.

Im going back to basis as the results are not matching. I found that the information extracted by crossing the data (without adding more columns), is not matching.

SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F ) >>> please could you be more specific with this part of the query? -- this is for checking existence of ID's in table_F

AND CODE = 'OK'
Thank you
quote:
Originally posted by bandi

Did you display other columns of table_F along with SUM ?
If not,
SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F )
AND CODE = 'OK'
-- Chandu


show us sample data and output you want out of it


--
Chandu
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 06:50:46
select count (ID) from table_s this is the secondary table
1,000

select count (ID) from table_f this is the master table
10,000


select count (ID) from table_s as S
inner join file_f as F
on S.ID = F.ID
8,000


in the last query we can see that is incorrect the extraction as they have to be a total of 1,000 (the total file_s in file_f)

So I need to cross the data and have 1,000 as an output. If that's is Ok, I will add the columns that I need in the query after that.

Any tip?

Thank you





quote:
Originally posted by bandi

quote:
Originally posted by jfm

Yes I did.

Im going back to basis as the results are not matching. I found that the information extracted by crossing the data (without adding more columns), is not matching.

SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F ) >>> please could you be more specific with this part of the query? -- this is for checking existence of ID's in table_F

AND CODE = 'OK'
Thank you
quote:
Originally posted by bandi

Did you display other columns of table_F along with SUM ?
If not,
SELECT SUM (BALANCE)
FROM table_S AS S
WHERE ID IN (SELECT ID FROM table_F )
AND CODE = 'OK'
-- Chandu


show us sample data and output you want out of it


--
Chandu

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 06:52:46
what does this return?

select count (distinct S.ID) from table_s as S
left join file_f as F
on S.ID = F.ID



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 07:04:11
Ok

thanks for distinct I was missing that part.

So now this query i giving to me the same extraction as the total file_s

Let me work on my big query and I will get back to you.

Thanks a lot

quote:
Originally posted by visakh16

what does this return?

select count (distinct S.ID) from table_s as S
left join file_f as F
on S.ID = F.ID



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-26 : 07:25:30
ok fine

you're welcome

let us know if you need any more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

jfm
Posting Yak Master

145 Posts

Posted - 2013-04-26 : 11:46:21

SELECT F.I_date, F.N_Date, S.S_Date, S.Number/100 FROM Table_S AS S
LEFT JOIN Table_F AS F
ON S.ID = F.ID

??

SUM(CASE WHEN S_Date-F.N_Date <'1' THEN S_Number/100 END) as '0' ,
SUM(CASE WHEN S_Date-F.N_Date ='1' THEN S_Number/100 END) as '1' ,
SUM(CASE WHEN S_Date-F.N_Date ='2' THEN S_Number/100 END) as '2' ,
SUM(CASE WHEN S_Date-F.N_Date ='3' THEN S_Number/100 END) as '3' ,


WHERE S.Ref = 'ok'
GROUP BY F.I_date
ORDER BY F.I_date


What I need is this extraction. By using the query we have talked about and with the SUM(CASE ...

The first problem was extract the balance in Table_S (secondary table), by connecting the tables using the only link: Id_Col.

But to extract my data, I need to see the difference between dates_columns and extract a matrix from the SQL as is written: SUM(CASE ...

Im very close to the solution, but im missing something.

Can you help me in this final step?

Thank you







quote:
Originally posted by visakh16

ok fine

you're welcome

let us know if you need any more help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs


Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-26 : 12:44:46
Help us to help you!

http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-29 : 00:28:54
something like


SELECT F.I_date,
SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) < 1 THEN S_Number/100 END) as [0] ,
SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) = 1 THEN S_Number/100 END) as [1] ,
SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) = 2 THEN S_Number/100 END) as [2] ,
SUM(CASE WHEN DATEDIFF(dd,S_Date,COALESCE(F.N_Date,S_Date)) = 3 THEN S_Number/100 END) as [3]
FROM Table_S AS S
LEFT JOIN Table_F AS F
ON S.ID = F.ID
WHERE S.Ref = 'ok'
GROUP BY F.I_date
ORDER BY F.I_date


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -