CODEWITHSUNDEEP
sqlmariadb
Jack
Jack

Reputation: 3415

Select multiple rows that match the same IDs requested

I can't seem to find anything like what I'm trying to do.

I have multiple IDs, for instance: 9044, 9044, 3832. Note how some IDs can be the same.

If I use IN to get this, such as:

select `name`, `value`, item_id
from items
where item_id IN (9044,9044,3832) and tradeable = 1

It will return only two results even though I requested for 3:

enter image description here

How can I modify my query to match the amount of items requested even if the item ids are the same? Thanks

Upvotes: 0

Views: 43

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The general solution is to use a left join:

select i.name, i.value, ii.item_id
from (select 9044 as item_id union all
      select 9044 union all
      select 3832
     ) ii left join
     items i
     on i.item_id = ii.item_id and i.tradeable = 1;

Upvotes: 1

The Impaler
The Impaler

Reputation: 48770

In SQL IN is a set operator. One of the first tasks the SQL optimizer does is to remove repeated elements in the set, since it doesn't make sense to have multiple equivalent values.

If you really want to get three rows, you could try something like:

select name, value, item_id from items where item_id = 9044 and tradeable = 1
union all
select name, value, item_id from items where item_id = 9044 and tradeable = 1
union all
select name, value, item_id from items where item_id = 3832 and tradeable = 1;

Upvotes: 1

Related Questions