Search
Search

MySQL single table subquery

debugcn Published at Dev
10
dls

I have a problem where I wish to take all the rows in a table with col5=2206 and then select all other rows with col2 and col3 containing the same combination of col2 and col3 as that of col5 (fi. in the example below col2=2, col3=6)

I also wish to filter the returned rows to those with certain values in col5 (fi. col5=4000).

('101', '2', '6', '2009-12-31', '2206', 'Exempt', '0', '0', '0', '4', '5'),  
('102', '2', '6', '2009-12-31', '4000', 'Exempt', '-1', '0', '0', '4', '5'),  
('103', '2', '6', '2009-12-31', '1200', '', '1', '0', '0', '4', '5');

I have tried various sub query statements. But can't get anything to work as on one table. Is this possible or do I need to create a bigger script.

collapsar

You use self joins to achieve what you want.

 Select t2.*
   From table t1
   Join table t2 on ( t2.col2 = t1.col2 and t2.col3 = t1.col3 )
  Where t1.col5 = '2206'
    And t2.col5 <> '2206' -- replace that with specific filters like t2.col5 = '4000'
      ;

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MYSQL math in a single column in a table. Subquery not grouping correctly

From Dev

Update table in MySQL with subquery

From Dev

Avoiding a nested subquery for the single table

From Dev

mySQL Subquery - Single pass if possible

From Dev

MySQL Update with same table subquery

From Dev

Perform a subquery on a spatial table in MySQL

From Dev

MySQL - Referencing an aliased table in a subquery

From Dev

MySQL subquery and temporary table is slow

From Dev

Using subquery for the same table in MySQL

From Dev

MySQL Update with same table subquery

From Dev

MySQL subquery and temporary table is slow

From Dev

subquery mysql on Update in same table

From Dev

MySQL count subquery on same table

From Dev

Mysql single query with subquery or two separate queries

From Java

MySQL UPDATE TABLE with subquery does not execute subquery first

From Dev

MySQL reference outer table alias in subquery error

From Dev

MYSQL Subquery on the same table with Group By Clauses...

From Dev

Mysql subquery COUNT in same Table returns NULL

From Dev

MySQL Subquery with Averages From Another Table

From Dev

MySQL: Add a condition on a subquery from a joint table

From Dev

MYSQL Subquery on the same table with Group By Clauses...

From Dev

MySQL subquery count with calendar table slow

From Dev

MySQL table for single column

From Dev

MySQL update a table and select from the same table in a subquery

From Dev

Mysql single table very slow

From Dev

MySQL, single table multiple columns

From Dev

Mysql complex issue on a single table

From Dev

Displaying MySQL results in a single table

From Java

mysql with few tables, subquery on one large table performs slow

Related Related

  1. 1

    MYSQL math in a single column in a table. Subquery not grouping correctly

  2. 2

    Update table in MySQL with subquery

  3. 3

    Avoiding a nested subquery for the single table

  4. 4

    mySQL Subquery - Single pass if possible

  5. 5

    MySQL Update with same table subquery

  6. 6

    Perform a subquery on a spatial table in MySQL

  7. 7

    MySQL - Referencing an aliased table in a subquery

  8. 8

    MySQL subquery and temporary table is slow

  9. 9

    Using subquery for the same table in MySQL

  10. 10

    MySQL Update with same table subquery

  11. 11

    MySQL subquery and temporary table is slow

  12. 12

    subquery mysql on Update in same table

  13. 13

    MySQL count subquery on same table

  14. 14

    Mysql single query with subquery or two separate queries

  15. 15

    MySQL UPDATE TABLE with subquery does not execute subquery first

  16. 16

    MySQL reference outer table alias in subquery error

  17. 17

    MYSQL Subquery on the same table with Group By Clauses...

  18. 18

    Mysql subquery COUNT in same Table returns NULL

  19. 19

    MySQL Subquery with Averages From Another Table

  20. 20

    MySQL: Add a condition on a subquery from a joint table

  21. 21

    MYSQL Subquery on the same table with Group By Clauses...

  22. 22

    MySQL subquery count with calendar table slow

  23. 23

    MySQL table for single column

  24. 24

    MySQL update a table and select from the same table in a subquery

  25. 25

    Mysql single table very slow

  26. 26

    MySQL, single table multiple columns

  27. 27

    Mysql complex issue on a single table

  28. 28

    Displaying MySQL results in a single table

  29. 29

    mysql with few tables, subquery on one large table performs slow

HotTag

Archive