CODEWITHSUNDEEP
mysqlsql
Learner
Learner

Reputation: 21425

combining two not related tables and some conditions

I have a items table with 3 columns

request_id, item_id, item_weight

Example:

101, 1, 10
102, 2, 30
103, 3, 60
104, 4, 80

Now I have another table called movers with 2 columns:

name  ,  max_weight

Lets say sample data in movers table is like this:

name = A, max_weight = 100
name = B, max_weight = 50
name = C, max_weight = 20

My task is to check each item_weight and compare if it is less than max_weight and the say yes or no in result column.

Query result (the order of columns is also needed like first B_mover then A_mover then C_mover):

request_id, item_id, B_mover, A_mover, C_mover
101,            1,   Y          Y       Y
102,            2,   Y          Y       N
103,            3,   N          Y       N
104,            4,   N          Y       N

I am stuck on how to join these tables and then get results with these conditions.

Upvotes: 0

Views: 43

Answers (2)

Mackers
Mackers

Reputation: 1050

I don't believe what you're trying to do is viable if your movers table has a dynamic number of rows. This is possible if the number of rows is static; however, the query would get rather unwieldy if you had several rows, since you would need a join or clause per row.

Alternatively, you could create a query that output the following columns:

request_id, item_id, mover_id, less_than_mover
101         1        A         Y
101         1        B         Y
101         1        C         Y
102         2        A         Y
102         2        B         Y
102         2        C         N
103         3        A         Y
103         3        B         N
103         3        C         N
104         4        A         Y
104         4        B         N
104         4        C         N

select
    i.request_id,
    i.item_id,
    m.mover_id,
    case when i.item_weight < m.max_weight then "Y" else "N" end as less_than_mover
from items i
join movers m
    on 1 = 1
`

Upvotes: 0

Paul Maxwell
Paul Maxwell

Reputation: 35603

Use cross join here, this produces a cartesian product of all rows in items multiplied by all rows in movers, then use conditional aggregation to summarise and pivot the data as per your requested layout.

select
      i.request_id
    , i.item_id
    , i.item_weight
    , max(case when m.name = 'a' and i.item_weight > m.max_weight then 'Y' else 'N') as A_mover
    , max(case when m.name = 'b' and i.item_weight > m.max_weight then 'Y' else 'N') as B_mover
    , max(case when m.name = 'c' and i.item_weight > m.max_weight then 'Y' else 'N') as C_mover
from items as i
cross join movers as m
group by 
      i.request_id
    , i.item_id
    , i.item_weight

Note. If you only need some movers such as a+b+c then cross join a subquery like this:

from items as i
cross join (select * from movers where name in ('a','b','c') ) as m

Upvotes: 1

Related Questions