1

I'd like to find all rows if is shows even once a particular value with postgresql

For example :

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
  3  M.        BOUVIER   Alain       V
  4  M.        DUBOIS    Paul        D
  5  M.        DREYFUS   Jean        C
  6  M.        FAURE     Alain       C
 11  M.        DUPONT    Alain       B
 12  M.        DUVAL     Arsène      B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

And I want to have all rows where CLI_ENSEIGNE = 'A' but not only I want also the rows where CLI_NOM had a CLI_ENSEIGNE = 'A' even only one time:

We see here the Dupond had an A the a B

id   TIT_CODE  CLI_NOM   CLI_PRENOM  CLI_ENSEIGNE
  1  M.        DUPONT    Alain       A
  2  M.        MARTIN    Marc        A
 11  M.        DUPONT    Alain       B
 13  M.        DUPONT    Alain       B
 16  M.        CHABAUD   Daniel      A

I tried

SELECT 
  *
FROM 
  table
WHERE 
CLI_ENSEIGNE = 'A' 
IN (CLI_NOM)

But it's saying that CLI_NOM doesn't exists

  • 1
    Is there a chance you have a DUPONT Daniel in the table? – ypercubeᵀᴹ Feb 24 '14 at 19:58
  • And why isn't the row with id=13 returned? – ypercubeᵀᴹ Feb 24 '14 at 19:58
  • My mistake id=13 should return – Eddinho Feb 24 '14 at 20:29
  • So please click "edit" left under your question to fix it. – Erwin Brandstetter Feb 24 '14 at 20:53
2

You where clause is incomplete and wouldn't work that way anyway.

You need a subselect for this:

SELECT   *
FROM the_table
WHERE cli_nom in (select cli_nom 
                  from the_table
                  where CLI_ENSEIGNE = 'A' );

alternatively a co-related sub-select with an exists clause:

SELECT t1.*
FROM the_table t1
WHERE EXISTS (select 42
              from the_table t2
              where t2.cli_nom = t1.cli_nom
                and CLI_ENSEIGNE = 'A' );
  • omg I've got it all wrong, thank you man – Eddinho Feb 24 '14 at 20:27
0

Try:

select * from x where cli_ensiegne ='A' or cli_nom in (select cli_nom from x where cli_ensiegne ='A')

Dupont had B 2 times.

  • Yes you're right I should have 2 rows of Dupond with B – Eddinho Feb 24 '14 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.