CODEWITHSUNDEEP
phpmysqlsqljoin
Monojit
Monojit

Reputation: 157

Mysql Query Join two table Order by one table column

I have two table (table 1 and table 2) . I want to show all the rows from table1 by joining with table2 which have multiple rows with same table1 id (foreign key relation) and will sort the result by table2 priority column (order by desc).

Table1

enter image description here

Table2

enter image description here

Result will be

enter image description here

thanks in advance

Edit

Table1

id   name

1   test1 
2   test2
3   test5
4   test7
5   test9
6   test3


Table2


id  table1_id   event   priority

1   2             abc      0
2   2             kbc      0
3   2             abc      2
4   2             kbc      1
5   4             fgg      2
6   4             dss      3
7   1             fgfg     2
8   5             fgfg     2
9   6             xcxc     1
10  6             fgfh     3

Result

id_table1   name    event   priority

4   test7   dss     3
6   test3   fgfh    3
2   test2   abc     2
1   test1   fgfg    2
5   test9   fgfg    2
3   test5   NULL    NULL

Upvotes: 2

Views: 6451

Answers (4)

TheProvost
TheProvost

Reputation: 1893

This will get the result set that you want. You mentioned that you only need the items table1 ids that reflects more than once but result query shows tableid1 "1" even though it is only present once:

SELECT DISTINCT t1.id,t1.name ,t2.event, t2.priority
FROM TABLE2 t2
right join 
TABLE1 t1
on t1.id=t2.table1_id
order by t2.priority desc

Upvotes: 0

marttronix
marttronix

Reputation: 9

Primary key and foreign key should have the same name. The syntax should be

SELECT Table1.id_table1,Table1.name,Table2.event,Table.priority FROM
Table1 LEFT JOIN Table2 ON
Table1.id=Table2.id
ORDER BY Table2.priority DESC

Make the following changes in Table2:

  1. Get rid of the first column or rename it
  2. Rename second column(your foreign key) to "id".

Upvotes: -1

sudin
sudin

Reputation: 332

Try this query:

SELECT t1.*,t2.priority FROM table1 t1
INNER JOIN table2 t2
ON t1.id=t2.id
ORDER BY t2.priority DESC

Upvotes: -1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

In the question you mentioned you need to select the data where id from table1 is available more than once in the table2 which does not match with the result set you gave.

Considering the original requirement the following should do the trick

select
t2.table1_id as id_table1,
t1.name,
t2.priority,
t2.event
from table1 t1
join
(
  select 
  p1.table1_id,
  p1.event,
  p2.priority
  from table2 p1
  join(
    select
    max(priority) as priority,
    table1_id
    from table2
    group by table1_id having count(*) > 1
  )p2
  on p2.table1_id = p1.table1_id and p2.priority = p1.priority

)t2
on t1.id = t2.t1id
order by t2.priority desc

Here is a demo

The result will get the same event corresponding the max priority column

Upvotes: 3

Related Questions