Join nested / multi query with a single query

Question

How could i join this multi query with just a single mysql query ?

$query = $mysql->query("select rate from postages where city=$city");

if($query->num_rows == 0)
{
    $query = $mysql->query("select rate from postages where subdistrict=$subdistrict");

    if($query->num_rows == 0)
    {
        $query = $mysql->query("select rate from postages where district=$district");           
    }
}

Thanks for your help.

Do you expect your query to follow the conditions above? If no rate available for the city, then get the rate for the subdistrict, etc.? — Josh
– Josh, Commented Jan 21, 2012 at 6:45
Is each query expected to return only one row or are there multiple rows with the same city and different subdistricts for example? — Joachim Isaksson
– Joachim Isaksson, Commented Jan 21, 2012 at 7:13
Ok, then the (second) answer from @JasonMitchell is the same as what I came up with, no need to dupe it :) — Joachim Isaksson
– Joachim Isaksson, Commented Jan 21, 2012 at 7:49

GordonM · Accepted Answer · 2012-01-21 07:13:22Z

3

You could UNION the queries together, but you'd need something in your result so you could tell if it was a city, district or sub district. Something like the following might work, though I've obviously not been able to test it.

(
    SELECT 
        city AS city,
        NULL AS district, 
        NULL AS subdistrict,  
        rate AS rate 
    FROM postages 
    WHERE city=$city
) UNION (
    SELECT 
        NULL,
        district, 
        NULL,
        rate
    FROM postages 
    WHERE city=$city
    AND district=$district
) UNION (
    SELECT 
        NULL, 
        NULL, 
        subdistrict,  
        rate
    FROM postages 
    WHERE city=$city
    AND district=$district
    AND subdistrict=$subdistrict
)

If the result set contains at least one row where city is not null, one where district is not null and one where subdistrict is not null then you've gotten a valid result set.

edited Jan 21, 2012 at 7:13

answered Jan 21, 2012 at 7:00

GordonM

31.9k17 gold badges94 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Josh Over a year ago

+1 This is similar to what I was trying to wrap my head around.

Joachim Isaksson Over a year ago

This will still return both city rows and district rows if they both match which the original won't.

Joachim Isaksson Over a year ago

Still; $city='a', $subdistrict='b' will match a row with city='c', subdistrict='b' in the original query, not in yours (although I'm liking your result better, it's not the same)

GordonM Over a year ago

@JoachimIsaksson: I think I've hit the limit of help I can offer, I'm afraid. Hopefully you'll at least have some ideas from this that can help you out though. Without knowing what you're trying to do I can only infer from the code you posted.

Zul · Accepted Answer · 2012-01-21 09:13:49Z

2

If you wanted to get the exact same result as you have in your example you could do:

SELECT IFNULL(c.rate, IFNULL(d.rate, s.rate)) as rate FROM (
    (SELECT rate, count(*) FROM postages where city = '$city') as c,
    (SELECT rate, count(*) FROM postages where district = '$district') as d,
    (SELECT rate, count(*) FROM postages where subdistrict = '$subdistrict') as s
)

You could also do it another way... Which only runs the district query if where is the city query returns 0 rows, and only runs the subdistrict call if the district query returns 0 rows:

SELECT IFNULL(
        (SELECT rate FROM postages where city = '$city'),
     IFNULL(
       (SELECT rate FROM postages where district = '$district' limit 1),
       (SELECT rate FROM postages where subdistrict = '$subdistrict' limit 1)
     )
) as rate

edited Jan 21, 2012 at 9:13

Zul

3,6063 gold badges24 silver badges36 bronze badges

answered Jan 21, 2012 at 7:31

Jason Mitchell

2891 silver badge4 bronze badges

3 Comments

Joachim Isaksson Over a year ago

The first always returns 0 if the city doesn't match (not sure how that happens, the city query individually returns c.rate as null but nullif sees c.rate as 0) while the second crashes if there are multiple lines with the same district and the city doesn't match (Subquery returns more than one row)

Joachim Isaksson Over a year ago

The comments on the question confirm there's only one match per query in the original, which makes your second version quite correct :)

Jason Mitchell Over a year ago

This is kind of an interesting question because there are so many different ways to do it.

Collectives™ on Stack Overflow

Join nested / multi query with a single query

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related