Search
Search

select rows which has latest date without repetition from two joined table?

debugcn Published at Dev
10
W.Cointreau

I have table A and B.

Table A has data like

|  id  |    status    | made at | ... |
|  1   |     new      |14-04-14 | ... |
|  2   |    fixed     |14-08-12 | ... |
|  3   |    fixed     |14-03-15 | ... |
| ...  |     ...      |  ..     | ... |

and in Table B,

|  id  |   A_id   |    changes   |changed at| ... |
|  1   |    1     |     new      | 14-04-14 | ... |
|  2   |    2     |     new      | 14-08-12 | ... |
|  3   |    2     |     fixed    | 14-08-28 | ... |
|  4   |    3     |     new      | 14-03-15 | ... |
|  5   |    3     |    fixed     | 14-05-11 | ... |
|  6   |    3     |    fixed     | 14-05-14 | ... |
|  ..  |   ..     |      ..      |   ..     | ... |

What I want as the result is to pick what has fixed status in table A and fixed changes in table B, with no repetition.

If it has same changes like 5 and 6 in B, I will pick only changed at latest data.

So the result will look like...

|  id  |  A_id   |  made at  |  status  |  changes  |  changed at |  ...  |
|  2   |   2     | 14-08-12  |  fixed   |   fixed   |  14-08-28   |  ...  |
|  3   |   3     | 14-03-15  |  fixed   |   fixed   |  14-05-14   |  ...  |

I tried select * from A, B where (A.status='fixed') and (A.id=B.A_id) and (B.changes='fixed') but still have repetition result in changes.

How can I make query right?

Sandeep Nambiar

Try This Query

  SELECT tab.* FROM(SELECT A.*,B.A_id,B.chnages,B.changed at 
 FROM  A LEFT JOIN B ON A.id=B.A_id
 WHERE A.status='fixed' AND B.changes ='fixed' 
 ORDER BY B.changes_at DESC ) as tab GROUP BY tab.id

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 select row with two matching joined rows from another table

From Dev

Select latest from joined table excluding duplicates

From Dev

Query to select related data from two tables in which one table has no related fields in third table

From Dev

Select all entries from one table which has two specific entries in another table

From Dev

How to group rows and select latest date to display in a data table

From Dev

SELECT latest data from 2 joined tables

From Dev

How to select rows from SQLite table which fall between today's date and 7 days time

From Dev

Select latest Rows by date and time

From Dev

Using max() to get the latest rows in a table, joined on another table

From Dev

Use LISTAGG to select multiple rows on joined table

From Dev

SQL Select rows from table where all joined rows match value

From Dev

How to select last record by date in a joined table

From Dev

SELECT 4 latest rows from Oracle table with join

From Dev

Oracle SQL: Select rows from table A with fallback to joined table A and B. (union, group by,...)

From Dev

How to select latest date from this query (not in a existing table)?

From Dev

Mysql: Select from parent table, only if child table has rows

From Dev

How to delete all rows from table which has no FK relation

From Dev

MYSQL select max date from joined tables

From Dev

Getting max date from a joined table with group by

From Dev

Select from joined table only if record exists

From Dev

select record from joined table if it exists

From Dev

Select only duplicates from inner joined table

From Dev

Unable to select data from another joined table

From Dev

Getting two values from same Joined table

From Dev

Update rows from select which return us table

From Dev

select the latest date from joins

From Dev

Select n rows from a table that has 2 types of choices

From Dev

Select two rows per key with the latest time

From Dev

Select two rows per key with the latest time

Related Related

  1. 1

    MySQL select row with two matching joined rows from another table

  2. 2

    Select latest from joined table excluding duplicates

  3. 3

    Query to select related data from two tables in which one table has no related fields in third table

  4. 4

    Select all entries from one table which has two specific entries in another table

  5. 5

    How to group rows and select latest date to display in a data table

  6. 6

    SELECT latest data from 2 joined tables

  7. 7

    How to select rows from SQLite table which fall between today's date and 7 days time

  8. 8

    Select latest Rows by date and time

  9. 9

    Using max() to get the latest rows in a table, joined on another table

  10. 10

    Use LISTAGG to select multiple rows on joined table

  11. 11

    SQL Select rows from table where all joined rows match value

  12. 12

    How to select last record by date in a joined table

  13. 13

    SELECT 4 latest rows from Oracle table with join

  14. 14

    Oracle SQL: Select rows from table A with fallback to joined table A and B. (union, group by,...)

  15. 15

    How to select latest date from this query (not in a existing table)?

  16. 16

    Mysql: Select from parent table, only if child table has rows

  17. 17

    How to delete all rows from table which has no FK relation

  18. 18

    MYSQL select max date from joined tables

  19. 19

    Getting max date from a joined table with group by

  20. 20

    Select from joined table only if record exists

  21. 21

    select record from joined table if it exists

  22. 22

    Select only duplicates from inner joined table

  23. 23

    Unable to select data from another joined table

  24. 24

    Getting two values from same Joined table

  25. 25

    Update rows from select which return us table

  26. 26

    select the latest date from joins

  27. 27

    Select n rows from a table that has 2 types of choices

  28. 28

    Select two rows per key with the latest time

  29. 29

    Select two rows per key with the latest time

HotTag

Archive