join perfection - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

join perfection

Author

Topic

scottichrosaviakosmos
Yak Posting Veteran

66 Posts

Posted - 2010-09-06 : 03:25:17

i have a table named Rose with following data:
Brachname status amount

london A 100 (varchar)
london A 200
menchester A 300
Paris D 600

now i am using a query and storing the dat in two sererate temporary tables ,one with status A and other with
Status D

select branchname as branch ,sum(convert(decimal(18,2),amount) as active into #tmpA from Rose where status='A'
select branchname as branchN, sum(convert(decimal(18,2), amount) as cancel into #tmpB from rose where status='D'

now when i am using query :
select * from #tmpA a join #tmpB on a.branchname=b.branchname
when i run this query i m getting one one row for Status D but i want all data, and second thing is that i m getting cancel
column data as 600 in london , manchester and paris where it should show 600 in only paris and 0 for rest .
This example is just a dumy and not real tables or columns but situation is same .

scoo

Kristen
Test

22859 Posts

Posted - 2010-09-06 : 03:33:58

"when i run this query i m getting one one row for Status D but i want all data,"

You should not be getting ANY rows with the data above! If your data actually has more rows and includes one with Paris D and another with Paris A then you will get that row in your resultset.

A JOIN is used to retrieve data from tables where it exists in BOTH tables. Your query will not find data that only exists in ONE table.

You can use an Outer Join:


SELECT ...
FROM TableA AS A
     LEFT OUTER JOIN TableB AS B
         ON B.MyID = A.MyID

but this is not what you want either. This will take ALL rows from TableA, regardless of whether a matching row exists in TableB, and will also retrieve matching rows from TableB where any/many exist.

To combine two tables and get rows from both you either need a FULL OUTER JOIN (which I doubt) or I think more likely a UNION:


SELECT ColA1, ColA2, ...
FROM TableA
UNION
SELECT ColB1, ColB2, ...
FROM TableB
UNION
SELECT ColC1, ColC2, ...
FROM TableC

you must have the same number of columns in all the SELECT statements in the UNION(s). If you use UNION then any rows that are exactly duplicates in all the columns in the SELECT statement will be removed; if you want the duplicates retained then use UNION ALL instead. (You should also use UNION ALL if you know that there are NO duplicates - as it will be faster / more efficient).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-06 : 03:38:40

Oh. Boy...

1) Your SELECT INTO is missing an ending group by
2) Your SELECT INTO is missing an ending parathesis for the SUM clause
3) Your final SELECT is missing an b alias for #tmpB table

And all you need is this simple query.

SELECT		BranchName,
		SUM(CASE WHEN [Status] = 'A' THEN Amount ELSE 0 END) AS Active,
		SUM(CASE WHEN [Status] = 'D' THEN Amount ELSE 0 END) AS Cancel
FROM		dbo.Rose
GROUP BY	BranchName

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources