Inner/outer join

[kreeben]

I have a table with (amongst others) the columns "pid" and "countrycode" (char and varchar). Pid stands for "PageId". I want a recordset that includes the pid for the countrycode "se" and also includes the pid for the cuntrycodes "de" and "eu", if there are any pid:s for these countries. If a pid is missing from either "eu" or "de" I still want to include the row.

My table columns:

|countrycode|pid|

I want my recordset to look like this:

|pid|se.countrycode|de.countrycode|eu.countrycode|
_________________________________________________
|startpage|se|de|eu| (if "startpage" is defined for all countrycodes)

|startpage|se|de|null| (if "startpage" is defined for the countrycodes "se" and "de")

Am I making any sense?

It doesn´t matter if I use "INNER", "OUTER", "LEFT", "RIGHT" or "FULL" joins, i always get the same no good result.

Here´is my statement, where I have tried with only two different countrycodes:

SELECT se.pid AS PAGE, se.CountryCode AS SWEDEN, de.CountryCode AS GERMANY
FROM wa_PageContent se LEFT OUTER JOIN
wa_PageContent de ON se.pid = de.pid
WHERE (se.KeyPage = 1) AND (se.CountryCode = 'se') AND (de.KeyPage = 1) AND (de.CountryCode = 'de')
ORDER BY se.Script, se.pid, se.SortOrder

 

[kreeben]

Well, to clarify, the statement in my last thread gives me a result where only those cases where pid exists for both "se" and "de" are included.

What I want is a complete list for countrycode = "se" and "de". If a pid is missing from "de" the the result should be NULL, or something.

Can anyone help me, or are y´all to busy following the election ;-)

 

[kreeben]

In this thread:
Query Help: records in table X but not in Y.

http://www.tek-tips.com/viewthread.cfm?qid=942543&page=1

...there´s a query that gives you "X.ITEM's not in Y". I want a result that gives me "X.ITEM _NOT_ IN Y" as well ass "X.ITEM IN Y". If "X.ITEM _NOT_ IN Y" then I want NULL in the result.

 

[kreeben]

Nevermind... I solved it by counting the number of instances of PageId form each CountryCode. If a PageId does not exist for a specific CountryCode then the result is ZERO.

SELECT pid AS PAGE,
(SELECT COUNT(*)
FROM wa_pagecontent
WHERE pid = base.pid AND countrycode = 'se') AS SWEDISH,

(SELECT COUNT(*)
FROM wa_pagecontent
WHERE pid = base.pid AND countrycode = 'eu') AS ENGLISH,

(SELECT COUNT(*)
FROM wa_pagecontent
WHERE pid = base.pid AND countrycode = 'de') AS GERMAN
FROM wa_PageContent base
WHERE (KeyPage = 1)
ORDER BY pid

 

[PHV]

Have you tried something like this ?
SELECT se.pid AS PAGE, se.CountryCode AS SWEDEN, de.CountryCode AS GERMANY
FROM wa_PageContent se LEFT OUTER JOIN (
SELECT pid, CountryCode FROM wa_PageContent
WHERE (KeyPage = 1) AND (CountryCode = 'de')
) de ON se.pid = de.pid
WHERE (se.KeyPage = 1) AND (se.CountryCode = 'se')
ORDER BY se.Script, se.pid, se.SortOrder


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244