mysql - SELECT from two tables based on the same id and grouped -


i have 2 tables different structure (table1 confirmed items, table2 items waiting confirmation, each user may have more items in either table):

table1 id (primary) | user_id | group_id | name | description | active_from | active_to 

and

table2 id (primary) | user_id | group_id | name | description | active_from 

what try have list of items of user - i.e. rows both tables same user_id (e.g. 1) prepared shown grouped group_id (i.e. first group 1, group 2 etc.) ordered name (inside each group). output should that:

(all groups below belong same user - user_id)  # group 1 (group_id) # item 67 (id): apple (name), healthy fruit (description) (item stored in table1) item 29: pear, rounded fruit (item stored in table2)  # group 2 # item 14: grape, juicy fruit (item stored in table2)  # group 3 # item 116: blackberry, shining fruit (item stored in table2) item 14: plum, blue fruit (item stored in table1) item 7: raspberry, red fruit (item stored in table1) 

i not able have working solution, tried using join simple select 2 tables using where clause.

i ended following code not working since returning wrong (much higher - redundant) number of results (not talking non-implemented ordering of results table2):

select table1.id, table1.user_id, table1.group_id, table1.active_from, table1.active_to, table2.id, table2.user_id, table2.group_id, table2.active_from table1 left join table2 on table1.user_id = table2.user_id (table1.group_id='".$group_id."' or table2.group_id='".$group_id."') , (table1.user_id='".$user_id."' or table2.user_id='".$user_id."') order table1.property_name asc 

a union suit problem. data massage required give both sides of union same number , type of columns:

select  group_id ,       id item_id ,       name ,       description ,       source_table    (         select  id         ,       user_id         ,       group_id         ,       name         ,       description         ,       'from table1' source_table            table1         union         select  id         ,       user_id         ,       group_id         ,       name         ,       description         ,       'from table2'  -- column name defined above            table2         ) subqueriesmustbenamed   user_id = 1 order         group_id ,       name 

working example @ sql fiddle.

to format result set like, iterate on result set. when group_id changes, print # group n # header.

there should no need have other loops or iterations client-side, 1 foreach or equivalent on set of rows returned query.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -