0

Example mysql

SELECT * FROM listings WHERE (
1 IN (SELECT id FROM listings_x WHERE listings_x.type = listings.type) AND
2 IN (SELECT id FROM listings_x WHERE listings_x.type = listings.type) AND
3 IN (SELECT id FROM listings_x WHERE listings_x.type = listings.type) AND
)

Is there a better way than doing the same select statement multiple times. I havent been able to figure this one out, and I dont really like this method as it doesnt seem all that efficient.

Kevin Upton
  • 3,336
  • 2
  • 21
  • 24
  • I think you can put the subquery on the left-side; don't go quoting me if I'm wrong though –  Feb 07 '15 at 21:08

2 Answers2

2

Or, you could try joining ahead of time:

SELECT * FROM listings
JOIN listings_x ON listings_x = listings.type
WHERE listings_x.id BETWEEN 1 AND 3;

The thing that comes after the ON keyword is the join condition. By moving the join condition to the from clause, the join does not have to be repeated increasing efficiency and decreasing the amount of typing.

The join condition is the same condition you have been using in the where clause; it has only been moved to the from clause.

  • about this answer, I want to know what is more efficient, in or between, in this case when you try to find number values, maybe you can answer me or i will need to create a question :). – Juan Ruiz de Castilla Feb 07 '15 at 21:13
  • I'm not sure, I'm no expert; I'm only an amateur –  Feb 07 '15 at 21:13
  • This would be correct if the id column is an integer (a reasonable assumption) and if conditions were `or`, but they are `and`. – Gordon Linoff Feb 07 '15 at 21:16
  • No, this is correct @GordonLinoff because AND is part of the `BETWEEN` keyword's syntax; my example is the equivalent of writing `WHERE listings_x.id >= 1 AND listings_x.id <= 3` Also, considering the information given and the fact the author seems competent, it is **highly unlikely** that the id is not an integer. –  Feb 07 '15 at 21:19
  • @Isaiah . . . Your code will return rows that match 1, 2, or 3. Not necessarily all three values. The OP wants all three values for each listing . . . or at least, that is what the query does in the question. – Gordon Linoff Feb 07 '15 at 21:20
  • 1
    According to my question @isaiah and if you want to know, there is a post talk about between and in performance,http://stackoverflow.com/questions/3308280/is-there-a-performance-difference-between-between-and-in-with-mysql-or-in-sql-in – Juan Ruiz de Castilla Feb 07 '15 at 21:21
  • GordonLinoff , Apology for intruding , you are right !, we don't really understood the question , whatever I do not know if He (post owner ) really want to make that question ... . – Juan Ruiz de Castilla Feb 07 '15 at 21:45
  • ahh this works nice. I changed the method from between to an and statement. for example: `listing_x.id = 1 AND listing_x.id = 3` as they werent necessarily between **a** and **b**. – Kevin Upton Feb 07 '15 at 21:46
1

You want all three types for the listings.

Assuming there is a listings id, then the following gets you the listings ids with all three:

select l.id
from listings l join
     listings_x lx
     on lx.type = l.type
where lx.id in (1, 2, 3)
group by l.id
having count(distinct lx.id) = 3;

You can take advantage of a MySQL (mis) feature that allows for columns in the select not to be in the group by because you are aggregating by a unique key. So:

select l.*
from listings l join
     listings_x lx
     on lx.type = l.type
where lx.id in (1, 2, 3)
group by l.id
having count(distinct lx.id) = 3;

EDIT:

If you wanted to do this with the same structure that you have (using a where clause rather than group by), you can do:

SELECT l.*
FROM listings l
WHERE EXISTS (SELECT 1
              FROM listings_x x
              WHERE x.type = l.type and x.id in (1, 2, 3)
              HAVING COUNT(DISTINCT x.id) = 3
             )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786