CODEWITHSUNDEEP
mysqlsql
John Peeterson
John Peeterson

Reputation: 81

SELECT from two tables based on the same id and grouped

I have two tables with different structure (table1 confirmed items, table2 items waiting for 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 I try to have is the list of all the items of a certain user - i.e. rows from both tables with the same user_id (e.g. 1) prepared to be shown grouped by group_id (i.e. first group 1, then group 2 etc.) ordered by name (inside each group). Output should look like that:

(all the groups below belong to the same user - with certain 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 am not able to have a working solution, I tried using JOIN as well as simple SELECT from two tables using WHERE clause.

I ended with the following code which is obviously not working since returning wrong (much higher - redundant) number of results (not talking about non-implemented ordering of the results from 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
FROM table1
LEFT JOIN table2
ON table1.user_id = table2.user_id
WHERE (table1.group_id='".$group_id."' OR table2.group_id='".$group_id."') AND (table1.user_id='".$user_id."' OR table2.user_id='".$user_id."')
ORDER BY table1.property_name ASC

Upvotes: 2

Views: 4052

Answers (3)

Swetha reddy
Swetha reddy

Reputation: 71

select * from a t1 , b t2 where t1.user_id=t2.user_id and t1.group_id='' ORDER BY t1.name ASC

Upvotes: 1

Andomar
Andomar

Reputation: 238086

A union would suit your problem. Some data massage is required to give both sides of the union the same number and type of columns:

select  group_id
,       id as item_id
,       name
,       description
,       source_table
from    (
        select  id
        ,       user_id
        ,       group_id
        ,       name
        ,       description
        ,       'from table1' source_table
        from    table1
        union all
        select  id
        ,       user_id
        ,       group_id
        ,       name
        ,       description
        ,       'from table2'  -- Column name is already defined above
        from    table2
        ) as SubQueriesMustBeNamed
where   user_id = 1
order by
        group_id
,       name

Working example at SQL Fiddle.

To format the result set as you like, iterate over the result set. When the group_id changes, print a # Group N # header.

There should be no need to have other loops or iterations client-side, just one foreach or equivalent over the set of rows returned by the query.

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 94914

Okay, from what you say in your comments section I gather that the two tables are not related. So we don't want to JOIN them.

You want to get results of the union of the two data sets (i.e. the two tables, or more precisely: the user's records of the two tables). So use UNION, or better: UNION ALL, (as Andomar already suggested).

You don't want any GROUP BY, because GROUP BY in SQL means to aggregate data (i.e. get one result line per group). You want to ORDER BY group_id instead:

select *
from
(
  select group_id, id, name, description, 'confirmed' as status 
  from table1 where user_id = 12345
  union all
  select group_id, id, name, description, 'unconfirmed' as status
  from table2 where user_id = 12345
)
order by group_id;

Upvotes: 0

Related Questions