Transforming 2 columns subquery into 1 with MySQL

Question

I'm having a little trouble with a specific query. Suppose you have a website like ebay, and you want to select the users that sold at least 2 objects, but which are of different types (clothes, electronics, etc.)

Here is what I came up with :

Select name, count(id_object) from user u,advert a where u.id_user=a.id_seller and 
a.id_buyer IS NOT NULL and 
id_object in (select o1.id_object, o2.id_object from object o1, object o2
where o1.id_object != o2.id_object and o1.type_object != o2.type_object)
group by name
having count(id_object)>1

The problem occur here:

id_object in (select o1.id_object, o2.id_object from object o1, object o2
where o1.id_object != o2.id_object and o1.type_object != o2.type_object)

This does not work because I pull 2 columns from the sub select and id_object is only one.

My second attempt involved separating the object1 and object 2 but since they need to be different, the added "in" returns nothing.

My third attempt was to UNION the results from object 1 and 2, but I wasn't able to place a restriction on the fact that the object1 has to be different from objet2.

If it helps, here is the simplified architecture of the tables :

Object (id_object, type_object)
Advert (id_seller, id_buyer, id_object)
User (id_user, name)

I also searched stackoverflow for similar problems without any success : MySQL: Using "In" with Multiple SubQueries? MySQL: Returning multiple columns from an in-line subquery

LSA · Answer 1 · 2017-05-12T17:42:58.460

1

Try something like this.

Edit: Damn forgot about that type thing, try the following. Edit 2: We do not want the objects of same type, but different. Please try again, slowly getting ugly. Edit 3: fixed number of occurrences

Select * from user where id_user in (
    Select c.id_seller from (
        Select a.id_seller as id_seller, 
               o.object_type as oject_type,
               count(*) as numOccurrences
        from adverts a, object o 
    where o.id_object=a.id_object 
    group by a.id_seller having count(*) > 1) c 
where c.numOccurrences > 1);

edited May 12 '17 at 17:42

answered May 08 '17 at 20:37

LSA

404
7
11

Your query works but returns me the name of a user that only sold the same type twice. I am trying to see why this happens because your group by seems fine – Maude May 08 '17 at 20:51
you are completely right, edited again, hopefully better now :-) – LSA May 08 '17 at 21:13
There is some issue with this part : `Select a.id_seller as id_seller, o.object_type as oject_type from adverts a, object o where o.id_object=a.id_object group by a.id_seller having count(*) > 1` When I remove the group by, I get the sellers with all the categories they sold. So far so good. But when I apply the group by seller, he picks the seller ID but only 1 type of object. He doesn't keep the second one. So c.object_type is = to 1 which doesn't work. Putting =1 instead of >1 works, but i'm wondering if it will work for every case I need? – Maude May 08 '17 at 21:47

score 1 · Accepted Answer · answered May 08 '17 at 21:46

I would do this as:

select a.id_user
from advert a join
     object o
     on a.id_object = o.id_object
group by a.id_user
having count(distinct o.type_object) > 1;

Because an object can have only one type, this satisfies the criteria. You could also add and count(distinct o.id_object) > 1, but that would be redundant.

If you want more user information, then you can join that table in, or use in, or use exists.

Transforming 2 columns subquery into 1 with MySQL

2 Answers2