Select rows in Postgresql where condition is fullfiled by all the members

0 votes

1 answer

950 views

                          I have a question about a topic that was already discussed: 



Let's say I have:

| ID | Name|Account|Year
| -------- | -------------- |------------|------------|
| 1   | Jack           |0001       |1928
| 2   | John           |0001 |1908
| 3   | Mary          |0001  |1918
| 4   | Paul          |0002  |1899
| 5  | James           |0002 |1913
| 6   | Laura           |0003  |1925

By using EXISTS clause as shown in the link:

    SELECT d.ID, d.Name d.Account, d.Year
    FROM data a
    WHERE EXISTS (
        SELECT 1 FROM data b WHERE Year < 1920 AND a.Account = b.Account
    ) ;

I obtain the Output:

| ID | Name|Account|Year
| -------- | -------------- |------------|------------|
| 1   | Jack           |0001       |1928
| 2   | John           |0001 |1908
| 3   | Mary          |0001  |1918
| 4   | Paul          |0002  |1899
| 5   | James           |0002 |1913

What query should i use in order to obtain:

| ID | Name|Account|Year
| -------- | -------------- |------------|------------|
| 4   | Paul          |0002  |1899
| 5   | James           |0002 |1913

If Year < 1920 at least for one line, no line from the "Account" field will not be selected.




                        

Asked by Paul Soare (1 rep)

Feb 15, 2023, 02:58 PM
Last activity: Feb 16, 2023, 01:13 AM

Select rows in Postgresql where condition is fullfiled by all the members

Related Questions