Mysql SELECT with an OR across 2 columns

Question

I'm creating a 'similar items' link table.

i have a 2 column table. both columns contains product ids.

CREATE TABLE IF NOT EXISTS `prod_similar` (
  `id_a` int(11) NOT NULL,
  `id_b` int(11) NOT NULL
)    

INSERT INTO `prod_similar` (`id_a`, `id_b`) VALUES
(5, 10),
(5, 15),
(10, 13),
(10, 14),
(14, 5),
(14, 13);

I want to select 3 similar products, favouring products where the id is in the first col, 'id_a'

SELECT * FROM prod_similar WHERE id_a={$id} OR id_b={$id}
ORDER BY column(?) 
LIMIT 3

Answer 1

Don't know, maybe this?

SELECT * 
FROM similar_items 
WHERE col_1={$id} OR col_2={$id} 
ORDER BY CASE WHEN col_1={$id} THEN 0 WHEN col_2={$id} THEN 1 END 
LIMIT 3

Answer 2

I am not sure I get the question, could you maybe post example data for the source table and also show what the result should look like.

If I got you right i would try something like

Select (case  
  when col_1={$ID}:  
    col1 
  when col_2={$ID}: 
    col2) as id from similar_items  WHERE col_1={$id} OR col_2={$id} 
LIMIT 3

Answer 3

An easy way to do this is this:

ORDER BY NULLIF(col_1, {$id}) LIMIT 3

The CASE WHEN works as well, but this is bit simpler.

Answer 4

I assume you have other columns as well

(SELECT 1 favouring, id_a id, [other columns]
FROM prod_similar
WHERE id_a = {$id})
UNION
(SELECT 2 favouring, id_b id, [other columns]
FROM prod_similar
WHERE id_b = {$id})
ORDER BY favouring, id
LIMIT 3;

In case you don't mind duplicates or there are none between id_a and id_b you can do UNION ALL instead which is considerably faster.

Unions are indication of denormalized data, denormalized data improves speed of certain queries and reduces speed of others (such as this).