SQL join allowing multiple rows for given column

Question

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?

Note that 30Q looks prettier on the page than cHJvZF81YWZhZmJkY2JiMzQ0OC4yMTg5MTY3OQ — Strawberry
– Strawberry, Commented May 29, 2018 at 14:03

sticky bit · Accepted Answer · 2018-05-29 11:41:30Z

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;

Ian-Fogelman · Accepted Answer · 2018-05-29 11:36:09Z

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

xQbert · Accepted Answer · 2018-05-29 11:40:49Z

0

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'

edited May 29, 2018 at 11:40

answered May 29, 2018 at 11:35

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

4 Comments

Wokes Over a year ago

group by shrinks it down to two rows, resulting in one product hash having only one applier attached to it. @sticky bit gave a working solution.

xQbert Over a year ago

That would depend if mySQL group by extensions are enabled or not. If not it should have errored. Don't really need the group by at all as no aggregation is occurring

Wokes Over a year ago

group by removes duplicates, as user can buy the same item more than once. I want to have just the total list of products purchased by given user for further processing. that's why the group by is there.

xQbert Over a year ago

Group by should only be used when aggregation is in use. Distinct should be used when you have duplicates in data that you want to eliminate; usually as a result of poor normalization in the database. The reasoning has to do with long term maintenance. Distinct tells the next person, yes there are duplicates and I intend to remove them. Group by may mean I had aggregation removed it, am inadvertently using the mySQL group by extensions to eliminate records ... so many variations. Just my 2cents.

Collectives™ on Stack Overflow

SQL join allowing multiple rows for given column

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related