Complex SQL request using join, distinct, count, and where

up vote
0
down vote

favorite

I have 2 tables: circuit(id_circuit, distance) and circuit_langue(id_circuit_language, #id_circuit, language, title).
I want to get the list of circuit which have more than 2 languages.

asked Nov 11 at 19:36

Jeff75

166

1

Hint: JOIN, GROUP BY and HAVING COUNT(*) >= 3
– Raymond Nijland
Nov 11 at 19:38

1

MySQL or Oracle? These are two different DBMS. You've tagged both.
– Thorsten Kettner
Nov 11 at 19:41

In this case the query is the same and works the same in Oracle or MySQL database @ThorstenKettner so the tags really does not matter that much.
– Raymond Nijland
Nov 11 at 19:42

@RaymondNijland Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too :)
– Barbaros Özhan
Nov 11 at 19:46

"Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too" Or the MySQL and Oracle tags can be removed from this question because the query needed for this is a basic ANSI SQL which works in most (R)DBMS @BarbarosÖzhan
– Raymond Nijland
Nov 11 at 19:50

|
show 3 more comments

up vote
0
down vote

favorite

I have 2 tables: circuit(id_circuit, distance) and circuit_langue(id_circuit_language, #id_circuit, language, title).
I want to get the list of circuit which have more than 2 languages.

asked Nov 11 at 19:36

Jeff75

166

1

Hint: JOIN, GROUP BY and HAVING COUNT(*) >= 3
– Raymond Nijland
Nov 11 at 19:38

1

MySQL or Oracle? These are two different DBMS. You've tagged both.
– Thorsten Kettner
Nov 11 at 19:41

In this case the query is the same and works the same in Oracle or MySQL database @ThorstenKettner so the tags really does not matter that much.
– Raymond Nijland
Nov 11 at 19:42

@RaymondNijland Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too :)
– Barbaros Özhan
Nov 11 at 19:46

"Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too" Or the MySQL and Oracle tags can be removed from this question because the query needed for this is a basic ANSI SQL which works in most (R)DBMS @BarbarosÖzhan
– Raymond Nijland
Nov 11 at 19:50

|
show 3 more comments

up vote
0
down vote

favorite

I have 2 tables: circuit(id_circuit, distance) and circuit_langue(id_circuit_language, #id_circuit, language, title).
I want to get the list of circuit which have more than 2 languages.

asked Nov 11 at 19:36

Jeff75

166

I have 2 tables: circuit(id_circuit, distance) and circuit_langue(id_circuit_language, #id_circuit, language, title).
I want to get the list of circuit which have more than 2 languages.

mysql sql oracle

asked Nov 11 at 19:36

Jeff75

166

asked Nov 11 at 19:36

Jeff75

166

asked Nov 11 at 19:36

Jeff75

166

asked Nov 11 at 19:36

Jeff75

166

asked Nov 11 at 19:36

Jeff75

166

1

Hint: JOIN, GROUP BY and HAVING COUNT(*) >= 3
– Raymond Nijland
Nov 11 at 19:38

1

MySQL or Oracle? These are two different DBMS. You've tagged both.
– Thorsten Kettner
Nov 11 at 19:41

In this case the query is the same and works the same in Oracle or MySQL database @ThorstenKettner so the tags really does not matter that much.
– Raymond Nijland
Nov 11 at 19:42

@RaymondNijland Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too :)
– Barbaros Özhan
Nov 11 at 19:46

"Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too" Or the MySQL and Oracle tags can be removed from this question because the query needed for this is a basic ANSI SQL which works in most (R)DBMS @BarbarosÖzhan
– Raymond Nijland
Nov 11 at 19:50

|
show 3 more comments

1

Hint: JOIN, GROUP BY and HAVING COUNT(*) >= 3
– Raymond Nijland
Nov 11 at 19:38

1

MySQL or Oracle? These are two different DBMS. You've tagged both.
– Thorsten Kettner
Nov 11 at 19:41

In this case the query is the same and works the same in Oracle or MySQL database @ThorstenKettner so the tags really does not matter that much.
– Raymond Nijland
Nov 11 at 19:42

@RaymondNijland Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too :)
– Barbaros Özhan
Nov 11 at 19:46

"Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too" Or the MySQL and Oracle tags can be removed from this question because the query needed for this is a basic ANSI SQL which works in most (R)DBMS @BarbarosÖzhan
– Raymond Nijland
Nov 11 at 19:50

Hint: JOIN, GROUP BY and HAVING COUNT(*) >= 3
– Raymond Nijland
Nov 11 at 19:38

MySQL or Oracle? These are two different DBMS. You've tagged both.
– Thorsten Kettner
Nov 11 at 19:41

In this case the query is the same and works the same in Oracle or MySQL database @ThorstenKettner so the tags really does not matter that much.
– Raymond Nijland
Nov 11 at 19:42

@RaymondNijland Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too :)
– Barbaros Özhan
Nov 11 at 19:46

"Then postgreSQL, SQL-Server may be added for the rest of the remaining tags too" Or the MySQL and Oracle tags can be removed from this question because the query needed for this is a basic ANSI SQL which works in most (R)DBMS @BarbarosÖzhan
– Raymond Nijland
Nov 11 at 19:50

|
show 3 more comments

3 Answers
3

active

oldest

votes

up vote
1
down vote

accepted

No join is necessary:

select cl.id_circuit

from circuit_langue cl

group by cl.id_circuit

having count(*) >= 3;

This assumes no duplicates in the table. If there are, then you want to use count(distinct language) >= 3.

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

if I do a join between circuit and circuit_langue, and it's possible that some objects from circuit don't have a circuit_langue, what i have to do if I want to recuperate objects without circuit_langue ?
– Jeff75
Nov 11 at 22:36

@Jeff75 . . . Your comment doesn't make sense in the context of this question. A circuit has to have three languages at least to be returned. Hence, you are not interested in those with no language.
– Gordon Linoff
Nov 11 at 23:12

add a comment |

up vote
0
down vote

You need to use group by with having like:

select c.id_circuit, count(cl.id_curcuit_language) as RecCount

from circuit c

inner join circuit_langue cl on c.id_circuit = cl.#id_circuit

group by c.id_circuit

having count(cl.id_curcuit_language) > 2

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

add a comment |

up vote
0
down vote

You may try this(oracle style):

select circuit.*

from circuit,

    (select id_circuit

    from circuit_langue

    group by id_circuit

    having count(*) >2 ) lang

where circuit.id_circuit = lang.id_circuit;

This query provides you whole rows from circuits table, so you can access any column. On the other hand, if you need only "id_circuit" column, then the answer of Gordon Linoff is the best.

answered Nov 11 at 20:41

Michael

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53252448%2fcomplex-sql-request-using-join-distinct-count-and-where%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

up vote
1
down vote

accepted

No join is necessary:

select cl.id_circuit

from circuit_langue cl

group by cl.id_circuit

having count(*) >= 3;

This assumes no duplicates in the table. If there are, then you want to use count(distinct language) >= 3.

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

if I do a join between circuit and circuit_langue, and it's possible that some objects from circuit don't have a circuit_langue, what i have to do if I want to recuperate objects without circuit_langue ?
– Jeff75
Nov 11 at 22:36

@Jeff75 . . . Your comment doesn't make sense in the context of this question. A circuit has to have three languages at least to be returned. Hence, you are not interested in those with no language.
– Gordon Linoff
Nov 11 at 23:12

add a comment |

up vote
1
down vote

accepted

No join is necessary:

select cl.id_circuit

from circuit_langue cl

group by cl.id_circuit

having count(*) >= 3;

This assumes no duplicates in the table. If there are, then you want to use count(distinct language) >= 3.

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

if I do a join between circuit and circuit_langue, and it's possible that some objects from circuit don't have a circuit_langue, what i have to do if I want to recuperate objects without circuit_langue ?
– Jeff75
Nov 11 at 22:36

@Jeff75 . . . Your comment doesn't make sense in the context of this question. A circuit has to have three languages at least to be returned. Hence, you are not interested in those with no language.
– Gordon Linoff
Nov 11 at 23:12

add a comment |

up vote
1
down vote

accepted

No join is necessary:

select cl.id_circuit

from circuit_langue cl

group by cl.id_circuit

having count(*) >= 3;

This assumes no duplicates in the table. If there are, then you want to use count(distinct language) >= 3.

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

No join is necessary:

select cl.id_circuit

from circuit_langue cl

group by cl.id_circuit

having count(*) >= 3;

This assumes no duplicates in the table. If there are, then you want to use count(distinct language) >= 3.

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

answered Nov 11 at 19:41

Gordon Linoff

751k34286394

if I do a join between circuit and circuit_langue, and it's possible that some objects from circuit don't have a circuit_langue, what i have to do if I want to recuperate objects without circuit_langue ?
– Jeff75
Nov 11 at 22:36

@Jeff75 . . . Your comment doesn't make sense in the context of this question. A circuit has to have three languages at least to be returned. Hence, you are not interested in those with no language.
– Gordon Linoff
Nov 11 at 23:12

add a comment |

if I do a join between circuit and circuit_langue, and it's possible that some objects from circuit don't have a circuit_langue, what i have to do if I want to recuperate objects without circuit_langue ?
– Jeff75
Nov 11 at 22:36

@Jeff75 . . . Your comment doesn't make sense in the context of this question. A circuit has to have three languages at least to be returned. Hence, you are not interested in those with no language.
– Gordon Linoff
Nov 11 at 23:12

if I do a join between circuit and circuit_langue, and it's possible that some objects from circuit don't have a circuit_langue, what i have to do if I want to recuperate objects without circuit_langue ?
– Jeff75
Nov 11 at 22:36

@Jeff75 . . . Your comment doesn't make sense in the context of this question. A circuit has to have three languages at least to be returned. Hence, you are not interested in those with no language.
– Gordon Linoff
Nov 11 at 23:12

add a comment |

up vote
0
down vote

You need to use group by with having like:

select c.id_circuit, count(cl.id_curcuit_language) as RecCount

from circuit c

inner join circuit_langue cl on c.id_circuit = cl.#id_circuit

group by c.id_circuit

having count(cl.id_curcuit_language) > 2

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

add a comment |

up vote
0
down vote

You need to use group by with having like:

select c.id_circuit, count(cl.id_curcuit_language) as RecCount

from circuit c

inner join circuit_langue cl on c.id_circuit = cl.#id_circuit

group by c.id_circuit

having count(cl.id_curcuit_language) > 2

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

add a comment |

up vote
0
down vote

You need to use group by with having like:

select c.id_circuit, count(cl.id_curcuit_language) as RecCount

from circuit c

inner join circuit_langue cl on c.id_circuit = cl.#id_circuit

group by c.id_circuit

having count(cl.id_curcuit_language) > 2

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

You need to use group by with having like:

select c.id_circuit, count(cl.id_curcuit_language) as RecCount

from circuit c

inner join circuit_langue cl on c.id_circuit = cl.#id_circuit

group by c.id_circuit

having count(cl.id_curcuit_language) > 2

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

answered Nov 11 at 19:38

Eray Balkanli

3,85741943

add a comment |

up vote
0
down vote

You may try this(oracle style):

select circuit.*

from circuit,

    (select id_circuit

    from circuit_langue

    group by id_circuit

    having count(*) >2 ) lang

where circuit.id_circuit = lang.id_circuit;

This query provides you whole rows from circuits table, so you can access any column. On the other hand, if you need only "id_circuit" column, then the answer of Gordon Linoff is the best.

answered Nov 11 at 20:41

Michael

add a comment |

up vote
0
down vote

You may try this(oracle style):

select circuit.*

from circuit,

    (select id_circuit

    from circuit_langue

    group by id_circuit

    having count(*) >2 ) lang

where circuit.id_circuit = lang.id_circuit;

This query provides you whole rows from circuits table, so you can access any column. On the other hand, if you need only "id_circuit" column, then the answer of Gordon Linoff is the best.

answered Nov 11 at 20:41

Michael

add a comment |

up vote
0
down vote

You may try this(oracle style):

select circuit.*

from circuit,

    (select id_circuit

    from circuit_langue

    group by id_circuit

    having count(*) >2 ) lang

where circuit.id_circuit = lang.id_circuit;

This query provides you whole rows from circuits table, so you can access any column. On the other hand, if you need only "id_circuit" column, then the answer of Gordon Linoff is the best.

answered Nov 11 at 20:41

Michael

You may try this(oracle style):

select circuit.*

from circuit,

    (select id_circuit

    from circuit_langue

    group by id_circuit

    having count(*) >2 ) lang

where circuit.id_circuit = lang.id_circuit;

This query provides you whole rows from circuits table, so you can access any column. On the other hand, if you need only "id_circuit" column, then the answer of Gordon Linoff is the best.

answered Nov 11 at 20:41

Michael

answered Nov 11 at 20:41

Michael

answered Nov 11 at 20:41

Michael

answered Nov 11 at 20:41

Michael

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky