CODEWITHSUNDEEP
mysqlsqldatabase
Rob T.
Rob T.

Reputation: 89

Selecting rows with more than one occurence of column value from joined table

I have a query which returns a joined table, it looks like the following:

| id_cart | id_customer | date_add              |
| ------- | ----------- | --------------------- |
| 1       | 100         | 2017-07-24 | 10:48:00 |
| ------- | ----------- | --------------------- |
| 2       | 101         | 2016-02-14 | 15:43:05 |
| ------- | ----------- | --------------------- |
| 3       | 100         | 2015-04-12 | 01:59:34 |

I'd like to make it so that from these results, only rows with more than one occurrence in the id_customer column are selected - I'm assuming it'd look something like this:

| id_cart | id_customer | date_add              | customer_count |
| ------- | ----------- | --------------------- | -------------- |
| 1       | 100         | 2017-07-24 | 10:48:00 | 2              |
| ------- | ----------- | --------------------- | -------------- |
| 3       | 100         | 2015-04-12 | 01:59:34 | 2              |

The issue I'm facing is most questions I've seen from others are looking for the count of multiple value occurrences, whereas I'm only interested in seeing the results - not the count. Also by using a joined table I'm unsure on how to refer back to it as a variable/alias, instead of just copy & pasting a horribly long query over and over again.

Upvotes: 2

Views: 54

Answers (1)

JohnHC
JohnHC

Reputation: 11195

For MySQL I would do this:

select *
from (select stuff from bigjoin) a1
inner join
(
select id_customer
from (select stuff from bigjoin)
group by id_customer
having count(*) > 1
) x1
on x1.id_Customer = a1.id_customer

Upvotes: 1

Related Questions