CODEWITHSUNDEEP
mysqlselectquery-optimizationmultiple-tablesmultiple-select
nawfal
nawfal

Reputation: 73173

Select from table if exists, else select from a totally different table

I have a query to fetch two values:

string query = @"SELECT price, weight
                 FROM   map 
                 WHERE  width = @width AND height = @height LIMIT 1";

if (_connSource.State != ConnectionState.Open)
    _connSource.Open();
MySqlCommand cmd = new MySqlCommand(query, _connSource);
cmd.Parameters.AddWithValue("width", width);
cmd.Parameters.AddWithValue("height", height);
r = cmd.ExecuteReader();

if (!r.Read())
{
    r.Close();

    query = @"SELECT retail_price, 0
              FROM   globe
              WHERE  PK_Id = @PK_Id LIMIT 1"

    cmd = new MySqlCommand(query, _connSource);
    cmd.Parameters.AddWithValue("PK_Id ", 1);
    r = cmd.ExecuteReader();
 }

What I need is to get price and weight according to a condition, but if it is not present in the table, then I need to get another two fields retail_price, and a constant 0 (doesnt matter what it is) from a totally new table with no constraints from the previous table. Can I get the two in a single query?

Note: Kindly give me optimized queries which doesn't force reading the same values more than once (this function gets executed thousands of times in one single operation, so speed is very critical - a reason why I'm trying to get it in one query). Thanks..

Upvotes: 3

Views: 3393

Answers (4)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726569

The syntax gets a little ugly, but I think you can do it:

(SELECT price, weight
 FROM map 
 WHERE width = @width AND height = @height LIMIT 1)
UNION ALL
(SELECT retail_price as price, 0 as weight
 FROM  globe
 WHERE PK_Id = @PK_Id LIMIT 1)

This returns 0 to 2 rows. If 2 rows are returned, pick the 1st one.

EDIT You can try avoiding the cost of the second query by using this monstrous construct. I am not sure if MySQL is going to handle it well, but it has a decent chance of avoiding the second query:

select
    ifnull(price, (select retail_price from globe where PK_Id = @PK_Id LIMIT 1))
,   ifnull(weight, 0)
from map 
WHERE width = @width AND height = @height LIMIT 1

Upvotes: 3

nawfal
nawfal

Reputation: 73173

I did get a pretty hacky solution to this, accidentally. Here is how one can do it:

SELECT price, weight
FROM map
WHERE width = @width AND height = @height LIMIT 1

UNION ALL

SELECT retail_price, 0
FROM  globe
WHERE PK_Id = @PK_Id LIMIT 1

The LIMIT 1 clause actually limits it from reading the second value if I get an answer in first select itself. Notice that I haven't added parentheses anywhere so that MySQL doesn't treat this as normal UNION ALL. Read more relevant info here and here

Apparently you can do this to make it more meaningful with this, but I do not think it performs any better:

SELECT price, weight
FROM map
WHERE width = @width AND height = @height LIMIT 1

UNION ALL

(SELECT retail_price, 0
FROM  globe
WHERE PK_Id = @PK_Id LIMIT 1) LIMIT 1

Here since I have added brackets, it works like normal UNION ALL, that is I should get both records if possible, but the last LIMIT 1 clause at the end of the query limits the result to the first set.

Upvotes: 1

k0alaDEV
k0alaDEV

Reputation: 61

If there is no Resultrow to the map WHERE then the DBNull will be replaced by globe values

SELECT 
    price = IFNULL(map.price,gl.price), 
    weight = IFNULL(map.weight,0)
FROM   map 
LEFT JOIN globe gl ON PK_Id = @PK_Id    
WHERE  width = @width AND height = @height 
LIMIT 1

Upvotes: 1

aingram
aingram

Reputation: 446

Since your "limit" clause guarantees a maximum of one row from each query, you could use a left join, and deal with existence of the "map" record using "IF()":

select ifnull(m.price,g.price), ifnull(m.weight,g.weight) from
(SELECT retail_price as price, 0 as weight
 FROM  globe
 WHERE PK_Id = @PK_Id LIMIT 1) g
LEFT JOIN
(SELECT price, weight
 FROM   map 
 WHERE  width = @width AND height = @height LIMIT 1) m
ON 1=1

The above query assumes that price and weight in the map table cannot be null.

Upvotes: 0

Related Questions