SEO

Hello,

I work with MySQL a long time but now I can't find a solution for a problem:

I've got a table (C_O) like this:

COUNTRY | ORGANISATION
--------+--------------
V | 1
V | 2
V | 3
G | 1
G | 2
G | 3
G | 4
U | 2
U | 3
U | 4
U | 5

Now I want to get all countries which are member in the same organisations
like the country 'V'. With the data from table above I'll get the county 'G'
but not 'U' (organisation '1' is missing).

My intension was:

SELECT COUNTRY AS PARTNER
FROM C_O AS mainTable
WHERE
(SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V') IN
(SELECT ORGANISATION FROM C_O WHERE COUNTRY = mainTable.Land_Kurzform);

But this don't work because the first subselect of course gives more than
one row and that's not allowed.

Maybe someone can give me a hint?

Thanks says Sven.

BTW: Sorry for my bad english ;-)

--
Internet: http://www.DunkelBuntes.de/ http://www.sReuter.net/
http://www.menschine.de/
ICQ: 116920585

"Sven Reuter" wrote:

> SELECT COUNTRY AS PARTNER
> FROM C_O AS mainTable
> WHERE
> (SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V') IN
> (SELECT ORGANISATION FROM C_O WHERE COUNTRY = mainTable.Land_Kurzform);
>

Sorry, there was a mistake. Right is:
SELECT COUNTRY AS PARTNER
FROM C_O AS mainTable
WHERE
(SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V') IN
(SELECT ORGANISATION FROM C_O WHERE COUNTRY = mainTable.COUNTRY);

"Sven Reuter" wrote:

> COUNTRY | ORGANISATION
> --------+--------------
> V | 1
> V | 2
> V | 3
> G | 1
> G | 2
> G | 3
> G | 4
> U | 2
> U | 3
> U | 4
> U | 5
>
> Now I want to get all countries which are member in the same organisations
> like the country 'V'. With the data from table above I'll get the county
> 'G' but not 'U' (organisation '1' is missing).
>

After one week thinking about it and posting my question here i'd the right
idea:

WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
SELECT COUNTRY, ORGANISATION
FROM C_O
WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V')
ORDER BY COUNTRY, ORGANISATION)
SELECT DISTINCT(COUNTRY) AS PARTNER
FROM TEMP_C_O AS mainTable
WHERE
(SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
(SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
mainTable.COUNTRY);

It's not very fast but right (I think).

:-)

Sven Reuter wrote:
> "Sven Reuter" wrote:
>
>> COUNTRY | ORGANISATION
>> --------+--------------
>> V | 1
>> V | 2
>> V | 3
>> G | 1
>> G | 2
>> G | 3
>> G | 4
>> U | 2
>> U | 3
>> U | 4
>> U | 5
>>
>> Now I want to get all countries which are member in the same
>> organisations like the country 'V'. With the data from table above
>> I'll get the county 'G' but not 'U' (organisation '1' is missing).
>>
>
> After one week thinking about it and posting my question here i'd the
> right idea:
>
> WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
> SELECT COUNTRY, ORGANISATION
> FROM C_O
> WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY =
> 'V') ORDER BY COUNTRY, ORGANISATION)
> SELECT DISTINCT(COUNTRY) AS PARTNER
> FROM TEMP_C_O AS mainTable
> WHERE
> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
> mainTable.COUNTRY);
>
> It's not very fast but right (I think).
>
> :-)

You should be using joins

"Paul Lautman" wrote:
> Sven Reuter wrote:
>> "Sven Reuter" wrote:
>> After one week thinking about it and posting my question here i'd the
>> right idea:
>>
>> WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
>> SELECT COUNTRY, ORGANISATION
>> FROM C_O
>> WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY =
>> 'V') ORDER BY COUNTRY, ORGANISATION)
>> SELECT DISTINCT(COUNTRY) AS PARTNER
>> FROM TEMP_C_O AS mainTable
>> WHERE
>> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
>> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
>> mainTable.COUNTRY);
>>
>> It's not very fast but right (I think).
>>
>> :-)
>
> You should be using joins
>
Instead of what? With only JOINs I came to no solution :-?

BTW: Both fields together are the primary key.

On 16 Dec, 20:09, "Sven Reuter" <ne...@dunkelbuntes.de> wrote:
> "Paul Lautman" wrote:
> > Sven Reuter wrote:
> >> "Sven Reuter" wrote:
> >> After one week thinking about it and posting my question here i'd the
> >> right idea:
>
> >> WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
> >> SELECT COUNTRY, ORGANISATION
> >> FROM C_O
> >> WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY =
> >> 'V') ORDER BY COUNTRY, ORGANISATION)
> >> SELECT DISTINCT(COUNTRY) AS PARTNER
> >> FROM TEMP_C_O AS mainTable
> >> WHERE
> >> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
> >> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
> >> mainTable.COUNTRY);
>
> >> It's not very fast but right (I think).
>
> >> :-)
>
> > You should be using joins
>
> Instead of what? With only JOINs I came to no solution :-?
>
> BTW: Both fields together are the primary key.

I just realised somethig, what version of MYSQL are you using that
supports the WITH clause?

"Captain Paralytic" :
> On 16 Dec, 20:09, "Sven Reuter" <ne...@dunkelbuntes.de> wrote:
>> "Paul Lautman" wrote:
>> > Sven Reuter wrote:
>> >> "Sven Reuter" wrote:
>> >> After one week thinking about it and posting my question here i'd the
>> >> right idea:
>>
>> >> WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
>> >> SELECT COUNTRY, ORGANISATION
>> >> FROM C_O
>> >> WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY =
>> >> 'V') ORDER BY COUNTRY, ORGANISATION)
>> >> SELECT DISTINCT(COUNTRY) AS PARTNER
>> >> FROM TEMP_C_O AS mainTable
>> >> WHERE
>> >> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
>> >> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
>> >> mainTable.COUNTRY);
>>
>> >> It's not very fast but right (I think).
>>
>> >> :-)
>>
>> > You should be using joins
>>
>> Instead of what? With only JOINs I came to no solution :-?
>>
>> BTW: Both fields together are the primary key.
>
> I just realised somethig, what version of MYSQL are you using that
> supports the WITH clause?

It have to be standard- more or less. And it should work with DB2. (I've
found no DB2-Group and my question was nonspecific respectively I'd to
rearange a given answer.)
In MySQL my query should work with temporary tables, I think.

"Sven Reuter" wrote:
> "Sven Reuter" wrote:
>
> After one week thinking about it and posting my question here i'd the
> right idea:
>
> WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
> SELECT COUNTRY, ORGANISATION
> FROM C_O
> WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V')
> ORDER BY COUNTRY, ORGANISATION)
> SELECT DISTINCT(COUNTRY) AS PARTNER
> FROM TEMP_C_O AS mainTable
> WHERE
> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
> (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
> mainTable.COUNTRY);
>
I've found a much faster alternative now (should also work with MySQL):

SELECT lo.COUNTRY
FROM C_O AS lo
WHERE (lo.COUNTRY <> 'V') AND
ORGANISATION IN
(SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V')
GROUP BY lo.COUNTRY
HAVING COUNT(lo.ORGANISATION) =
(SELECT COUNT(ORGANISATION) FROM C_O WHERE COUNTRY = 'V');

Just for people with a similar problem :-)

On 18 Dec, 06:47, "Sven Reuter" <ne...@dunkelbuntes.de> wrote:
> "Sven Reuter" wrote:
> > "Sven Reuter" wrote:
>
> > After one week thinking about it and posting my question here i'd the
> > right idea:
>
> > WITH TEMP_C_O (COUNTRY, ORGANISATION) AS (
> > SELECT COUNTRY, ORGANISATION
> > FROM C_O
> > WHERE ORGANISATION IN (SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V')
> > ORDER BY COUNTRY, ORGANISATION)
> > SELECT DISTINCT(COUNTRY) AS PARTNER
> > FROM TEMP_C_O AS mainTable
> > WHERE
> > (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY = 'V') =
> > (SELECT COUNT(ORGANISATION) FROM TEMP_C_O WHERE COUNTRY =
> > mainTable.COUNTRY);
>
> I've found a much faster alternative now (should also work with MySQL):
>
> SELECT lo.COUNTRY
> FROM C_O AS lo
> WHERE (lo.COUNTRY <> 'V') AND
> ORGANISATION IN
> (SELECT ORGANISATION FROM C_O WHERE COUNTRY = 'V')
> GROUP BY lo.COUNTRY
> HAVING COUNT(lo.ORGANISATION) =
> (SELECT COUNT(ORGANISATION) FROM C_O WHERE COUNTRY = 'V');
>
> Just for people with a similar problem :-)

You should be able to speed that up a bit more by:

SELECT lo.COUNTRY
FROM C_O lo
JOIN C_O c1 ON lo.organisation = c1.organisation and c1.country = 'V'
WHERE (lo.COUNTRY <> 'V')
GROUP BY lo.COUNTRY
HAVING COUNT(lo.ORGANISATION) =
(SELECT COUNT(ORGANISATION) FROM C_O WHERE COUNTRY = 'V');

There might be something to do with the latter sub-select, but I'll
need to play with the data a bit to check that out.