CODEWITHSUNDEEP
phpmysql
Haroldo
Haroldo

Reputation: 37377

Mysql SELECT with an OR across 2 columns

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

Upvotes: 1

Views: 204

Answers (4)

Unreason
Unreason

Reputation: 12704

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).

Upvotes: 2

reko_t
reko_t

Reputation: 56430

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.

Upvotes: 2

Cilvic
Cilvic

Reputation: 3447

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

Upvotes: 1

Jhonny D. Cano -Leftware-
Jhonny D. Cano -Leftware-

Reputation: 18013

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

Upvotes: 3

Related Questions