outer join on same table

roog · September 15th, 2004, 07:29 AM

I like to select all Countries where customertype = 'Consumer', together with all months available. So I like something like this:

France Januari
France Februari
null March
null April
UK Januari
etc..

I use only one table, so I have to make a "self"-join on this using the primary key "guide". I thought I could use a outer join (FULL to make it easy) like this:

SELECT a.Country, b.Month
FROM Data a FULL OUTER JOIN Data b
ON a.guide = b.guide
WHERE a.CustomerType = 'Consumer'

But this gives me only the months where Customertype = 'Consumer' !!

With a FULL outer join I would expect:
France null
France Januari
null Februari

I guess that I can't use the primary key, because it is non existent for those countries that have no month. How else can I solve this?

happygv · September 15th, 2004, 09:46 PM

Code:

SELECT Country, Month
FROM Data
WHERE CustomerType = 'Consumer'
ORDER BY Country

Does this help? Is this what you are looking for?

Cheers!

_________________________
- Vijay G
Strive for Perfection

happygv · September 15th, 2004, 09:57 PM

Roog,

It is enough that you post it once at one forum that best fit the subject of the post.

Cross post - http://p2p.wrox.com/topic.asp?TOPIC_ID=19198

_________________________
- Vijay G
Strive for Perfection

joefawcett · September 16th, 2004, 02:22 AM

Perhaps I've misunderstood, do you need a join at all?

Code:

SELECT CASE WHEN CustomerType = 'Customer' THEN Country ELSE NULL END AS Country, [Month] AS Month
FROM Data

--

Joe

Anantsharma · September 30th, 2004, 05:31 AM

Looking to the output required, I feel a GROUP BY on COUNTRY,MONTH will worK with ROLLUP.
----------------------
Select country,month from data
where customertype='CONSUMER'
group by country,month with ROLLUP
order by country,month
----------------------

With Rollup can be eliminated if u don't want the Group summery extra rows.

B. Anant