SQL server full join query

Question

5.00/5 (1 vote)

See more:

SQL-Server-2005

I have a full join sql query and I am retrieving the data from the same table.

The problem is I am getting the null value where I am expecting the column name.

Example:
I am having a table where there are two columns typeOfPost,dob.

DOB           TypeOfPost
---------    --------------
20/11/1998     Manager
1/1/2000       Sales
13/6/1999      Manager
20/1/1987      Manager
1/11/1985      Sales

Now when I am writing a join query like

SQL

select DATENAME(month,dob) as Red,count(TypeOfPost) 
from tablename
where TypeOfPost='Manager'
group by DATENAME(month,dob) as A

full join

select DATENAME(month,dob) as Green,count(TypeOfPost) 
from tablename
where TypeOfPost='Sales'
group by DATENAME(month,dob) as B on B.Green = A.Red

Output--                                Expected Output--
---------------------                   ---------------------
Month     Man     Sal                   Month     Man     Sal
--------  -----  ------                 --------  -----  ------
January    1       1                    January    1       1
NULL       1      NULL                  June       1      NULL
November   1       1                    November   1       1

Now here the problem rise, I want 'June' in the column Month instead of NULL value.
So is there any way to get that?
Help me out.
Thanks.

Posted 13-Nov-11 19:56pm

Ston Cold

Updated 13-Nov-11 22:45pm

Dalek Dave

v3

Add a Solution

Comments

Dalek Dave 14-Nov-11 4:45am

Edited for Grammar and Readability.

3 solutions

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)

Prerak Patel · Accepted Answer · 2011-11-13T20:04:00

Solution 1

I think you require to set date format before your query. SET DATEFORMAT dmy;
http://msdn.microsoft.com/en-us/library/ms189491.aspx[^]

[Edit]

SQL

select datename(month,dob) 'Month', sum(case when typeofpost='manager'THEN 1 ELSE 0 END) 'Man',sum(case when typeofpost='sales'THEN 1 ELSE 0 END) 'Sal'
FROM tablename
group by datename(month,dob)

Posted 13-Nov-11 20:04pm

Prerak Patel

Updated 13-Nov-11 20:28pm

v2

Comments

Ston Cold 14-Nov-11 2:12am

No its not working prerak..
still its showing the null value

Prerak Patel 14-Nov-11 2:18am

If you provide exact query and output, we might help you better. Your given query cannot produce this output any way. I just thought that date format could be the issue.

Ston Cold 14-Nov-11 2:26am

thats the exact query i have provided, dont worry about the column headings it will be different but the values are exact.

Prerak Patel 14-Nov-11 2:29am

You cannot achieve the provided output with the query in post. Check my query in answer.

Ston Cold 14-Nov-11 4:38am

Thanks prerak your query did worked for me..

Ston Cold 14-Nov-11 4:48am

One more question prerak if i want the count of manager and sales in other column then how would i get??

Example--
Total Count
-------------
2
1
2

in the above case.

Prerak Patel 14-Nov-11 5:38am

Yes, you can add those two sums in query.

Amir Mahfoozi · Accepted Answer · 2011-11-13T22:43:00

I made the table and inserted sample data in SSMS and ran this query. After execution I got your desired output :

SQL

select Red month, man, sal from
(select DATENAME(month,dob) as Red,count(TypeOfPost) man
from tablename
where TypeOfPost='Manager'
group by DATENAME(month,dob) ) a
full join
(select DATENAME(month,dob) as Green,count(TypeOfPost) sal
from tablename
where TypeOfPost='Sales'
group by DATENAME(month,dob) ) b on B.Green = A.Red

output was :

month                          man         sal
------------------------------ ----------- -----------
January                        1           1
June                           1           NULL
November                       1           1

I think the result is the one you wanted :-?
Is it ok ?

Ston Cold · Accepted Answer · 2011-11-13T22:39:00

Solution 2

SQL

select datename(month,dob) 'Month', sum(case when typeofpost='manager'THEN 1 ELSE 0 END) 'Man',sum(case when typeofpost='sales'THEN 1 ELSE 0 END) 'Sal'
FROM tablename
group by datename(month,dob);

Thanks to prerak patel..
Cheers

Posted 13-Nov-11 22:39pm