0

I've a query that I need some help with - As part of a form I've got a serial number field that is populated if there is a serial number, blank if it's not, or no result if it's an invalid serial number.

select * 
from cust_site_contract as cs 
where cs.serial_no = 'C20050' or (cs.serial_no <> 'C20050' and if(cs.serial_no = 'C20050',1,0)=0)
limit 10;

Here's a sample of the regular data:

+----------------------+-----------+-----------+-----------
| idcust_site_contract | system_id | serial_no | end_date
+----------------------+-----------+-----------+-----------
|               561315 | SH001626  | C19244    | 2009-12-21
|               561316 | SH001626  | C19244    | 2010-06-30
|               561317 | SH002125  | C19671    | 2010-05-31
|               561318 | SH001766  | C14781    | 2010-09-25
|               561319 | SH001766  | C14781    | 2011-02-15
|               561320 | SH002059  | C19020    | 2008-07-09
|               561321 | SH002639  | C18889    | 2008-03-31
|               561322 | SH002639  | C18889    | 2008-06-30
|               561323 | SH002715  | C20051    | 2010-04-30
|               561324 | SH002719  | C20057    | 2010-04-30

And an exact result would look something like this:

|               561487 | SH002837  | C20050    | 2012-07-04

I was writing this as a subquery so I could match the system_ids to customer and contract names, but realised I was getting garbage pretty early on. I'm tempted to try and simplify it by saying the third case might not hold true (i.e. if it's an invalid serial number, allow the choice of any customer name and simply flag it in the data) Has anyone got any ideas of where I'm going wrong? The combination of conditions is clearly wrong, and I can't work out how to make each side of the or statement mutually exclusive Even if I try to evaluate only the if(sn = 'blah') I get the wrong result for obvious reasons, but can't think of a sane way to express it. Many thanks Scott

Zane Bien
  • 22,685
  • 6
  • 45
  • 57
Scott
  • 347
  • 5
  • 16
  • Would it perhaps be simpler to have two queries? Try and get your specific result (select * where serial_no == XXXXXX) and then if you get no results (or 0, or null or whatever you wanna test for) then you could do a query to get every choice (as per question title)? – nbsp Jul 09 '12 at 04:56
  • I'm not sure if I completely understand your question. Do you want the query to return all rows if there is no serial number equal to `C20050`, but only return one row if there is? – Zane Bien Jul 09 '12 at 05:00
  • @Scott, if there does exist a serial number, which row do you wish for it to return? The most recent one (based on `end_date`)? The earliest one? Or just any like it doesn't matter? – Zane Bien Jul 09 '12 at 05:57
  • ideally the highest end_date - I've been checking through the data (it's a legacy oracle forms application) and I've found one serial number with dozens of identical sets of end dates, registered against the same serial number but multiple sites, so I'm currently trying to normalise the input - if I can start to create a reasonably intelligent data set, then I can hopefully trust that the highest end_date should then be unique, and provide a sane result ;) – Scott Jul 11 '12 at 05:28

2 Answers2

1

If there is is no contract with a serial number of C20050, this query will return all rows, otherwise, it will return only one row where serial_no is C20050:

SELECT a.*
FROM cust_site_contract a
INNER JOIN
(
    SELECT COUNT(*) AS rowexists
    FROM cust_site_contract
    WHERE serial_no = 'C20050'
) b ON b.rowexists = 0

UNION ALL

(
    SELECT *
    FROM cust_site_contract 
    WHERE serial_no = 'C20050'
    LIMIT 1
)
Zane Bien
  • 22,685
  • 6
  • 45
  • 57
  • 1
    Is the wrapper `SELECT` required at the second half of the union? http://stackoverflow.com/a/1415380/260007 – biziclop Jul 09 '12 at 05:24
  • @biziclop: Nope, I guess it's not. Thank you for that! – Zane Bien Jul 09 '12 at 05:26
  • Thanks Zane - that's perfect thanks It returns just what I'm after - I didn't know you could perform an inner join on a sub query! – Scott Jul 09 '12 at 06:01
  • I've been refactoring tables and removing garbage duplicates (44 records for the same number across different sites at the same company) so I've restricted the results to a more rational set, and I'm now averaging 0.018 seconds on 20k records - thanks again for your help Zane – Scott Aug 22 '12 at 03:09
  • `select distinct site_name from site s inner join (SELECT a.system_id FROM serial a INNER JOIN ( SELECT COUNT(*) AS rowexists FROM serial WHERE serial_no = 'C2010' ) b ON b.rowexists = 0 UNION ALL ( SELECT system_id FROM serial WHERE serial_no = 'C2010' LIMIT 1 )) as a on s.system_id = a.system_id order by site_name` – Scott Aug 22 '12 at 03:09
0

If you just write the query as below you will get blank if doesn't exists or it's an invalid serial number.

select cs.serial_no from cust_site_contract as cs where cs.serial_no = 'C20050'
Clinton Ward
  • 2,441
  • 1
  • 22
  • 26
  • Hi Clinton - not really, I need to return all results if there is no serial provided. A third possibility would be to return no results if and only if an invalid serial was provided. – Scott Jul 09 '12 at 05:56
  • @Scott, what would constitute as a valid serial number (certain number of characters?, numbers? etc) ? – Zane Bien Jul 09 '12 at 06:00
  • Hi Zane - valid serial numbers are in the form of either a valid 4-digit hex number, 5 digit alphanumeric, 8 digit integer or 12 digit integer, which makes it a bit of a mess ;) I'm in the process of trying to standardise support entitlement numbers, as a serial number isn't strictly required for software issues, only for hardware issues, but that's just too complex to address right now - I'd rather have people only have to find one number when they log an incident, rather than looking on a tiny silver sticker on a dongle, or a blue sticker on the back of a server – Scott Jul 09 '12 at 22:15