CODEWITHSUNDEEP
mysqlsqllaravel
Wokes
Wokes

Reputation: 113

SQL join allowing multiple rows for given column

I have a following query:

select transactions.timestamp, products.productHash
from transactions
inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash

giving me following result:

+---------------------+----------------------------------------+
|      timestamp      |              productHash               |
+---------------------+----------------------------------------+
| 2014-12-30 23:37:32 | cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ |
+---------------------+----------------------------------------+

I have to join it with materialAppliers where productHash is matching:

+----------------------------------------+------------------------------------------+-------+
|              productHash               |               applierHash                | asset |
+----------------------------------------+------------------------------------------+-------+
| cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2E1ZTExLjE3NDA3NjYx | val1  |
| cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2Q1MmE0LjI3NTA4Nzcx | val2  |
| cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NDdmNWQ0LjU1MzQ1NDg5 | val3  |
| cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NmY2MTA0LjQyOTkxNzY5 | val4  |
| cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5OTZlNGU3LjcxNTI1MDY1 | val5  |
+----------------------------------------+------------------------------------------+-------+

So that each materialApplier has its own row, consisting of timestamp and taken from previous query; applierHash and asset taken from materialAppliers:

+---------------------+----------------------------------------+------------------------------------------+-------+
|      timestamp      |              productHash               |               applierHash                | asset |
+---------------------+----------------------------------------+------------------------------------------+-------+
| 2014-12-30 23:37:32 | cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2E1ZTExLjE3NDA3NjYx | val1  |
| 2014-12-30 23:37:32 | cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ | bWF0QXBwXzVhZmFmYmRjY2Q1MmE0LjI3NTA4Nzcx | val2  |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NDdmNWQ0LjU1MzQ1NDg5 | val3  |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5NmY2MTA0LjQyOTkxNzY5 | val4  |
| 2014-11-30 19:17:47 | cHJvZF81YWZiZDVlOTQ3ZjM5Mi44NTc2Mjc0MQ | bWF0QXBwXzVhZmJkNWU5OTZlNGU3LjcxNTI1MDY1 | val5  |
+---------------------+----------------------------------------+------------------------------------------+-------+

How can I accomplish this?

Upvotes: 0

Views: 49

Answers (3)

sticky bit
sticky bit

Reputation: 37472

Just join materialAppliers to your query.

SELECT *
       FROM
(
select transactions.timestamp, products.productHash
from transactions
inner join transactionItems on transactionItems.transactionHash = tra nsactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash
) x
LEFT JOIN materialAppliers
          ON materialAppliers.productHash = x.productHash;

But you have a column in the list of columns, you don't GROUP BY and you're not applying any aggregation function on. Though MySQL accepts that in lower version or with certain settings that's not really a good thing. I recommend fixing this. Use e.g. max() to get the latest time stamp.

SELECT *
       FROM
(
select max(transactions.timestamp), products.productHash
from transactions
inner join transactionItems on transactionItems.transactionHash = tra nsactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash
) x
LEFT JOIN materialAppliers
          ON materialAppliers.productHash = x.productHash;

Upvotes: 1

xQbert
xQbert

Reputation: 35323

Seems pretty straight forward: add an inner join using materialAppliers and add two more selected values to the select... and group by am I missing something?

select transactions.timestamp, products.productHash, ma.applierHash, ma.asset
from transactions
inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
inner join materialAppliers ma on ma.productHash = products.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
    and (products.type = 'applier' and products.gender = 'f')
    and products.additionalData->'$."skintone"' in ('-1', -1)
    and exists
    (
        select 1 from materialAppliers
        where materialAppliers.productHash = products.productHash
            and materialAppliers.applierType = 'skin'
    )
group by transactionItems.productHash

You should e able to get rid of the exists and simply add and ma.applierType = 'skin' instead. I say should because I don't fully understand the relationship between applierType and the producthash but it looks like it should work.

such as:

select transactions.timestamp, products.productHash, ma.applierHash, ma.asset
from transactions
inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
inner join products on products.productHash = transactionItems.productHash
inner join materialAppliers ma on ma.productHash = products.productHash
where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
  and products.type = 'applier' 
  and products.gender = 'f'
  and products.additionalData->'$."skintone"' in ('-1', -1)
  and ma.applierType = 'skin'

Upvotes: 0

Ian-Fogelman
Ian-Fogelman

Reputation: 1605

I would try to alias the whole first query, then add the final join to the materialAppliers table after that 

Select * from (select transactions.timestamp, products.productHash
    from transactions
    inner join transactionItems on transactionItems.transactionHash = transactions.transactionHash
    inner join products on products.productHash = transactionItems.productHash
    where userKey = '8eb6a8a9-4f1a-4402-89ab-4d1fbffd1284'
        and (products.type = 'applier' and products.gender = 'f')
        and products.additionalData->'$."skintone"' in ('-1', -1)
        and exists
        (
            select 1 from materialAppliers
            where materialAppliers.productHash = products.productHash
                and materialAppliers.applierType = 'skin'
        )
    group by transactionItems.productHash) as x

inner join materialAppliers as ma on x.producthash = ma.productHash

Upvotes: 0

Related Questions