Doing a query on a ingenious database design. :s - Query help

Databases
1

Hellol,

First of all…

Can’t change the tables structure. :frowning:

I want to list, for a given .tld the registration prices per year on the cases where they are set. (this means, on the cases where the price (on a given currency) on a given period (Annual, biannual etc…) is greater then 0).

On one table tblpricing I have the prices of a given period:

CREATE TABLE `tblpricing` (
`id` int(10) NOT NULL auto_increment,
`type` enum('product','addon',
'configoptions','domainregister',
'domaintransfer','domainrenew',
'domainaddons') NOT NULL,
`relid` int(10) NOT NULL,
`currency` int(10) NOT NULL,
`msetupfee` decimal(10,2) NOT NULL,
`qsetupfee` decimal(10,2) NOT NULL,
`ssetupfee` decimal(10,2) NOT NULL,
`asetupfee` decimal(10,2) NOT NULL,
`bsetupfee` decimal(10,2) NOT NULL,
*PRIMARY KEY *(`id`)
) ENGINE=MyISAM AUTO_INCREMENT=349 DEFAULT CHARSET=latin1

I’ve found that (don’t ask me why):
msetupfee stores the price for 1 year;
qsetupfee stores the price for 2 years;
ssetupfee stores the price for 3 years;
asetupfee stores the price for 4 years;
bsetupfee stores the price for 5 years;

I’ve also found that the type(enum) is set to ‘domainregister’ - since we want the registration prices listed.

Lastly, found that relid corresponds to the ID of the next table:

On this other table, we have the .tld list:

CREATE TABLE `tbldomainpricing` (
`id` int(10) NOT NULL auto_increment,
`extension` text NOT NULL,
PRIMARY KEY (`id`),
KEY `extension_registrationperiod` (`extension`(32)),
KEY `order` (`order`)
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=latin1

The extension column contains the .tld values (.com, .net, .org….);

According to all this, I was trying to follow this path:

SELECT t1.extension, 
t2.msetupfee AS anual, 
t2.qsetupfee AS bienal, 
t2.ssetupfee AS trienal, 
t2.asetupfee AS quadrienal, 
t2.bsetupfee AS quinquenal
FROM `tbldomainpricing` AS t1
INNER JOIN `tblpricing` AS t2 
ON t1.id = t2.relid
WHERE t2.type = 'domainregister' 
AND t2.currency = 1 
AND t1.extension = '.com'

With this query I can list all the columns that I need listed as intended, however, if a column let’s say “msetupfee” or “qsetupfee” has the value of 0, I wish NOT to list it.

How can this be achieved?

Generally speaking:
“If a given row on a given column as a certain value, don’t list that column.” ?

Thanks in advance,
Márcio

2

Exactly. As you could with the 0 values.

3

that leading comma convention has had a very tough time gaining favour, so it’s nice to see someone else promoting it

thanks

:slight_smile: :slight_smile:

4

:s . I should be slapped. Ok.

So the code you have give as changed the 0 that I had by NULL. But the columns are still there.


extension 	  anual bienal trienal quadrienal quinquenal
.net 	      10.00  20.00   30.00 	NULL      NULL

And I wanted:


extension 	  anual bienal trienal
.net 	      10.00  20.00   30.00

:slight_smile: I can still make use of NULL later on - on PHP side perhaps.

5

all rows in the result set must have the same number of columns

therefore, the only thing you can do is place a NULL in that position in the result set

SELECT t1.extension
     , NULLIF(t2.msetupfee,0) AS anual
     , NULLIF(t2.qsetupfee,0) AS bienal
     , NULLIF(t2.ssetupfee,0) AS trienal
     , NULLIF(t2.asetupfee,0) AS quadrienal
     , NULLIF(t2.bsetupfee,0) AS quinquenal    
  FROM ...

:slight_smile:

6

You can’t. Not in MySQL. You can do that in (for example) PHP.

7

Oky. Thanks a lot Sirs. :wink: Again. :smiley:

Always learning,
Márcio

8

oikram, have a read of the section on this page of a SitePoint Article (http://articles.sitepoint.com/article/simply-sql-the-from-clause/3) about “Leading Commas”, they help you to spot stray commas or places where your missing a comma.

9

the query ~must~ return the ~same~ number of columns for ~all~ rows that satisfy the WHERE clause

10

you forgot the comma after t1.extension

11

Sorry, I thought that by doing that I will have NULL displayed, but true I haven’t test it.

I’ve tried this:


SELECT t1.extension NULLIF(t2.msetupfee,0) AS anual, 
NULLIF(t2.qsetupfee,0) AS bienal, 
NULLIF(t2.ssetupfee,0) AS trienal, 
NULLIF(t2.asetupfee,0) AS quadrienal, 
NULLIF(t2.bsetupfee,0) AS quinquenal 
FROM `tbldomainpricing` AS t1 
INNER JOIN `tblpricing` AS t2 ON t1.id = t2.relid 
WHERE t2.type = 'domainregister' 
AND t2.currency = 1 
AND t1.extension = '.com'

I got:

#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
‘NULLIF(t2.msetupfee,0) AS anual, NULLIF(t2.qsetupfee,0) AS bienal, NULLIF(t2.s’ at line 2

:frowning:

12

Given the scenario described, can you think on a better way for achieving (thinking a solution) to this?

I can convert them to NULL and then… then nothing because I can’t filter based on NULL conditions right… I mean we can… but not as intended right ?

(:

I will do it in PHP I’m however, wondering… if this can be done by using some other thinking.

13

i don’t understand what you’re saying

did you test what i gave you?