Sql select query issue join issue

Question

0.00/5 (No votes)

See more:

Hi guys please help me with this query

select TOP(1) SUM(ld.Id) as SUMOFTBL1,SUM(um.Id) as SUMOFTBL2 from LoginData ld,UploadMaster um

this gives me incorrect result

but if i do it seprately like
select TOP(1) SUM(ld.Id) as SUMOFTBL1 from LoginData ld
select TOP(1) SUM(um.Id) as SUMOFTBL2 UploadMaster um

it gives me correct result

my objective is to reduce sql code
and put this together...

Posted 19-Jun-13 6:02am

sunil mali

Add a Solution

Comments

Richard C Bishop 19-Jun-13 12:16pm

You need to do a join. You can't just select from multiple tables in your FROM clause.

sunil mali 19-Jun-13 12:38pm

I dont have any relationship between these tables,
It is just a report which shows count from different tables
My motive is just to reduce code... as i m howing count of multiple tables in single page..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2013-06-19T07:06:00

Solution 1

This is not a count. It is a SUM.

Select (select SUM(ld.Id) from LoginData ld) as SUMofTBL1, (select SUM(um.Id) from UploadMaster um) as SUMofTBL2

Here is a COUNT:

Select (select COUNT(*) from LoginData) as COUNTofTBL1, (select COUNT(*) from UploadMaster) as COUNTofTBL2

Posted 19-Jun-13 7:06am

Mike Meinz

Comments

sunil mali 19-Jun-13 14:01pm

Sir your answer is right, at first i was planning to do same as you suggested.. but i had lot of queries... which were combination of sum and count from multiple tables.... then i thought of joining this select queries... because result of my each query was one row... i am looking for kind of join here because result of my each query is single row... i dont know whther it is possible or not... i am just trying to find different ways of doing it to reduce my code....

Mike Meinz 19-Jun-13 14:12pm

The solution that I gave you yields a single row with two columns. Doesn't this answer the question that you posed?

sunil mali 20-Jun-13 1:07am

If this is the only way to do it then yes i should accept your answer.....

sunil mali 20-Jun-13 1:22am

this is my one query which is giving me sum of columns from single table
select SUM(wp.CapacityAlloc) as CapacityAlloc,SUM(wp.QC1Comp) as QC1Pending,(SUM(wp.CapacityAlloc)-SUM(wp.QC1Comp)) as QC1Completed,
SUM(wp.DEComp) as DEPending, (SUM(wp.CapacityAlloc)-SUM(wp.DEComp)) as DECompleted,
SUM(wp.QC2Comp) as QC2Pending , (SUM(wp.CapacityAlloc)-SUM(wp.QC2Comp)) as QC2Completed from WorkProgress wp where wp.BBId=@BBId

My 2nd query is this one
select
count(dm1.Id) as DiscrepancyRaised,count(dm2.Id) as DiscrepancyClosed,COUNT(sm1.Id) as Failed,COUNT(sm2.Id) as Success,COUNT(sm3.Id) as UW
from WorkProgress wp, DiscrepencyMaster dm1,DiscrepencyMaster dm2,UploadMaster um,StatusMaster sm1,StatusMaster sm2,StatusMaster sm3
where dm1.UploadId=um.Id and um.EmpId in(select um.EmpId from LoginData where BBId in(select BranchId from Branch_BPO_Mapping bbm where bbm.BPOId=@BBID)) and dm1.Status='Open'
and dm2.UploadId=um.Id and dm2.Status='Closed' and sm1.Status='Fail' and sm2.Status='Success' and sm3.Status='UW'

Both are giving me correct result...
If i have to combine these two queries then as per your logic
i have to write select statement for each column even if it is from same table

select((select SUM(wp.CapacityAlloc) as CapacityAlloc from WorkProgress wp where wp.BBId=@BBId),(select SUM(wp.QC1Comp) as CapacityAlloc from WorkProgress wp where wp.BBId=@BBId) vice versa...

as you can see i am writing select for each column and i am finding sum of it.
my moto is very straight forward...
i have two select statement, first statement is giving me correct result that is also with one where clause.. and one from clause....combining these two queryies will increase performance of my query i dont know how to do it... but i think there might be a way to do it...
i hope i am clear...

Mike Meinz 20-Jun-13 5:53am

You're spending too much time thinking about this. Just use the two queries separately!