Maude Maude - 6 months ago 27
MySQL Question

Transforming 2 columns subquery into 1 with MySQL

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

Answer Source

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.