IF statement within query

Databases
1

So I’m working a site that has a database with domains and with offers.
Two tables are used for this query: domeinen (domains) and domein_biedingen (domain_offers).

Domeinen has the following records of importance:

domein_id,
domein_naam,
domein_houder, (owner)
domein_datum,
domein_vraagprijs, (buy it now)
domein_startbod, (minimum offer)

domein_biedingen has the following records of importance:

bod_prijs, (price)
bod_domein, (same as domein_id)

Now what I’m doing is building a table that shows the domain and the offer amounts. Three columns for the name, date and owner and then two tables for the minimum offer and the maximum offer.

Now what I want is that if there are no offers on the domain yet, bod_prijs should reflect the minimum offer (domein_startbod). And if there are offers, bod_prijs to reflect the highest one.

In order to reflect the highest offer I used this query:


SELECT domein_id,domein_naam,domein_houder,domein_naam,domein_datum,domein_vraagprijs,domein_startbod,
(SELECT COUNT(*) FROM `domein_biedingen` WHERE `bod_domein` = domein_id) AS num_biedingen,MAX(bod_prijs) as bod_prijs
FROM domeinen LEFT JOIN domein_biedingen 
ON domeinen.domein_id = domein_biedingen.bod_domein GROUP BY domein_naam

But I can’t think up how to do the entire thing, I’m not sure if I can use an IF statement in here, and if that means I’m doing it wrong (I’m more experienced in pure php, and in php-eyes the following query seemed right).

Some sort of return functionality is required (I think) because I just need an if statement that checks if num_biedingen is below 1, and if that’s true put MAX(bod_prijs) as bod_prijs, and if it’s above 0 it should return domein_vraagprijs as bod_prijs.

This is what I tried:

SELECT domein_id,domein_naam,domein_houder,domein_naam,domein_datum,domein_vraagprijs,domein_startbod,
(SELECT COUNT(*) FROM `domein_biedingen` WHERE `bod_domein` = domein_id) AS num_biedingen,
(IF num_biedingen < 1 THEN domein_vraagprijs as bod_prijs; ELSE MAX(bod_prijs) as bod_prijs; END IF;)
FROM domeinen LEFT JOIN domein_biedingen
ON domeinen.domein_id = domein_biedingen.bod_domein GROUP BY domein_naam

I get this error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘num_biedingen < 1 THEN domein_vraagprijs as bod_prijs’ at line 3

I also tried some stuff without ; and with RETURN but I can’t figure out how it works.

Halp!

2

Works like a charm!
Thanks :slight_smile:

3

sorry, somehow i had an incorrect extra line of code in there

try like this –

SELECT d.domein_id
     , d.domein_naam
     , d.domein_houder
     , d.domein_naam
     , d.domein_datum
     , d.domein_vraagprijs
     , d.domein_startbod
     , [COLOR="Blue"]COALESCE(b.num_biedingen, 0) AS num_biedingen[/COLOR]  
     , [COLOR="Blue"]COALESCE(b.max_prijs, domein_startbod) AS bod_prijs[/COLOR]
  FROM domeinen AS d
LEFT OUTER
  JOIN ( SELECT bod_domein 
              , COUNT(*) AS num_biedingen
              , MAX(bod_prijs) AS max_prijs
           FROM domein_biedingen 
         GROUP
             BY bod_domein ) AS b
    ON b.bod_domein = d.domein_id
4

Cheers r937, that query taught me some new tricks :slight_smile:

5

do it like this –

SELECT d.domein_id
     , d.domein_naam
     , d.domein_houder
     , d.domein_naam
     , d.domein_datum
     , d.domein_vraagprijs
     , d.domein_startbod
     , b.num_biedingen
     , [COLOR="Blue"]COALESCE(b.max_prijs, domein_startbod) AS bod_prijs[/COLOR]
  FROM domeinen AS d
LEFT OUTER
  JOIN ( SELECT bod_domein 
              , COUNT(*) AS num_biedingen
              , MAX(bod_prijs) AS max_prijs
           FROM domein_biedingen 
         GROUP
             BY bod_domein ) AS b
          WHERE bod_domein = domein_id )
    ON b.bod_domein = d.domein_id

:cool:

6

Hm,

Always the second I give up and post on forums I find the answer :3
Didn’t realise there was a special IF statement for inside queries.
The only thing that bothers me now is even though this query works:

SELECT domein_id,domein_naam,domein_houder,domein_naam,domein_datum,domein_vraagprijs,domein_startbod,
(SELECT COUNT(*) FROM `domein_biedingen` WHERE `bod_domein` = domein_id) AS num_biedingen,
IF((SELECT COUNT(*) FROM `domein_biedingen` WHERE `bod_domein` = domein_id) &lt; 1,domein_startbod,MAX(bod_prijs)) as bod_prijs
FROM domeinen LEFT JOIN domein_biedingen
ON domeinen.domein_id = domein_biedingen.bod_domein GROUP BY domein_naam

I don’t understand why I have to do (SELECT COUNT(*) FROM domein_biedingen WHERE bod_domein = domein_id) again within the IF statement, if I declared that as num_biedingen before.
When I try to do it like this:

SELECT domein_id,domein_naam,domein_houder,domein_naam,domein_datum,domein_vraagprijs,domein_startbod,
(SELECT COUNT(*) FROM `domein_biedingen` WHERE `bod_domein` = domein_id) AS num_biedingen,
IF(num_biedingen &lt; 1,domein_startbod,MAX(bod_prijs)) as bod_prijs
FROM domeinen LEFT JOIN domein_biedingen
ON domeinen.domein_id = domein_biedingen.bod_domein GROUP BY domein_naam

It gives me this error:

#1054 - Unknown column ‘num_biedingen’ in ‘field list’

This isn’t really a big problem but if it’s possible to clean this up I would like to do that ^^

PS: I just saw I use and not use ` like a moron, so I’m fixing that on my site.