SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 jfm
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jfm
Posting Yak Master

145 Posts

Posted - 04/26/2013 :  05:48:06  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/26/2013 :  05:59:54  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Germany
8768 Posts

Posted - 04/26/2013 :  06:01:01  Show Profile  Visit webfred's Homepage  Reply with Quote
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 - 04/26/2013 :  06:04:50  Show Profile  Reply with Quote
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 - 04/26/2013 :  06:07:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/26/2013 :  06:10:14  Show Profile  Reply with Quote
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 - 04/26/2013 :  06:23:35  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/26/2013 :  06:25:06  Show Profile  Reply with Quote
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

Edited by - visakh16 on 04/26/2013 06:27:14
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 04/26/2013 :  06:34:11  Show Profile  Reply with Quote
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 - 04/26/2013 :  06:50:46  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/26/2013 :  06:52:46  Show Profile  Reply with Quote
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 - 04/26/2013 :  07:04:11  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/26/2013 :  07:25:30  Show Profile  Reply with Quote
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 - 04/26/2013 :  11:46:21  Show Profile  Reply with Quote

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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/26/2013 :  12:44:46  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 04/29/2013 :  00:28:54  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000