|
|
Posted by Jim Michaels on 01/09/06 10:21
Book: SQL: The Complete Reference, Osborne/McGraw Hill
if you are talking about MySQL, there are control-flow functions under
"functions and operators" in the manual that can help you out.
If you use CASE, then you can handle future language expansion. (or maybe
that should be handled with a slightly different query)
IF FOUND sounds a lot like IF NULL(statement,statement2)
A subquery can return a scalar (a single value), a single row, a single
column, or a table (one or more rows of one or more columns). These are
called scalar, column, row, and table subqueries. Subqueries that return a
particular kind of result often can be used only in certain contexts, as
described in the following sections.
yours sounds like the need for a nested subquery.
There's not enough information in your question for me to give to a direct
answer, but I think I can arm you with better tools to work with.
You can install MySQL for free for dev purposes on your local box. it
includes a .CHM manual file on a Win32 installation. Older installations
had a huge .TXT file. sounds like you could use CASE and IFNULL, etc.
MySQL doesn't do Subqueries until release 4.1. (search in the manual for the
word Subquery) Most web servers probably aren't going to have an up-to-date
version like this.
example 1:
DELETE FROM t1
WHERE s11 > ANY
(SELECT COUNT(*) /* no hint */ FROM t2
WHERE NOT EXISTS
(SELECT * FROM t3
WHERE ROW(5*t2.s1,77)=
(SELECT 50,11*s1 FROM t4 UNION SELECT 50,77 FROM
(SELECT * FROM t5) AS t5)));
other examples:
SELECT CITY FROM OFFICES WHERE target > (SELECT SUM(quota) FROM salesreps
WHERE rep_office=office)SELECT company FROM customers WHERE cust_num IN
(SELECT DISTINCT cust FROM orders WHERE mfr='aci' AND product LIKE '4100%'
AND order_date BETWEEN '01-JAN-90' AND '30-JUN-90')SELECT DISTINCT
description FROM products WHERE EXISTS (SELECT order_num FROM orders WHERE
product=product_id AND mfr=mfr_id AND amount>=25000.00)WHERE X < ANY (SELECT
Y...) reads as "where, for some Y, X < Y"WHERE X < ALL (SELECT Y...)SELECT
company, CASE WHEN credit_limit > 60000 THEN 'a' WHEN credit_limit > 30000
THEN 'b' ELSE 'c' FROM customersSELECT city,SUM(sales) FROM
offices,salesreps WHERE office=CASE WHEN (rep_office IS NOT NULL) THEN
rep_office ELSE (SELECT rep_office FROM salesreps AS mgrs WHERE
mgrs.empl_num-manager)the ELSE in a CASE statement is not necessary of
course.COALESCE(,,,,,) with as many commas as you want, selects the first
non-NULL column in the comma-separated list.This eliminates your need for
2-columns results if you want it that way.SELECT
name,COALESCE(quota,sales,0.00) FROM salesreps
Here are some useful manual pages:
a.. CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN
result ...] [ELSE result] END , CASE WHEN [condition] THEN result [WHEN
[condition] THEN result ...] [ELSE result] END
The first version returns the result where value=compare-value. The second
version returns the result for the first condition that is true. If there
was no matching result value, the result after ELSE is returned, or NULL if
there is no ELSE part.
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
-> WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
-> WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL
The type of the return value (INTEGER, DOUBLE, or STRING) is the same as the
type of the first returned value (the expression after the first THEN).
CASE was added in MySQL 3.23.3.
a.. IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL) then IF() returns expr2,
else it returns expr3. IF() returns a numeric or string value, depending on
the context in which it is used.
mysql> SELECT IF(1>2,2,3);
-> 3
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
-> 'no'
If only one of expr2 or expr3 is explicitly NULL, the result type of the
IF() function is the type of non-NULL expression. (This behavior is new in
MySQL 4.0.3.)
expr1 is evaluated as an integer value, which means that if you are testing
floating-point or string values, you should do so using a comparison
operation.
mysql> SELECT IF(0.1,1,0);
-> 0
mysql> SELECT IF(0.1<>0,1,0);
-> 1
In the first case shown, IF(0.1) returns 0 because 0.1 is converted to an
integer value, resulting in a test of IF(0). This may not be what you
expect. In the second case, the comparison tests the original floating-point
value to see whether it is non-zero. The result of the comparison is used as
an integer.
The default return type of IF() (which may matter when it is stored into a
temporary table) is calculated in MySQL 3.23 as follows:
Expression Return Value
expr2 or expr3 returns a string string
expr2 or expr3 returns a floating-point value floating-point
expr2 or expr3 returns an integer integer
If expr2 and expr3 are strings, the result is case sensitive if either
string is case sensitive (starting from MySQL 3.23.51).
a.. IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2.
IFNULL() returns a numeric or string value, depending on the context in
which it is used.
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'
In MySQL 4.0.6 and above, the default result value of IFNULL(expr1,expr2) is
the more ``general'' of the two expressions, in the order STRING, REAL, or
INTEGER. The difference from earlier MySQL versions is mostly notable when
you create a table based on expressions or MySQL has to internally store a
value from IFNULL() in a temporary table.
CREATE TABLE tmp SELECT IFNULL(1,'test') AS test;
As of MySQL 4.0.6, the type for the test column is CHAR(4), whereas in
earlier versions the type would be BIGINT.
Jim Michaels
"Juliette" <jrf_no_spam@jokeaday.net> wrote in message
news:43847a0e$0$89138$dbd45001@news.wanadoo.nl...
> Hi all,
>
> I'm trying to reduce some query overhead by reducing the recordset
> returned from a query and I can't seem to get my head round how to do it
> using subqueries.
>
> Situation:
> Multiligual website with versionmanagement of the maintext and the
> translations.
> Each language has to be approved before it should be shown.
> Each text item has to be approved before it should be shown.
> Each translated item has to be approved before it should be shown.
> The translation may not be more than one version behind the default text
> version.
>
> If no (approved) translation to the requested language is found, the
> default language has to be displayed, assuming that there is always an
> approved version of the default language available.
>
> There are more criteria, but those aren't giving me problems, so this is
> what a simplified version of the (normalized relational) table layout
> looks like:
>
>
> Table text_items
> item_id | approved
> 001 | 2005-10-10
> 002 | 2005-11-01
> 003 | 2005-11-22
>
>
> Table text_content
> row_id | lang_id | item_id | item_version | translation_version |
> item_text | approved
> 001 | 001 | 001 | 1 | 1 | original text1| 2005-10-10
> 002 | 001 | 002 | 1 | 1 | original text2| 2005-11-01
> 003 | 001 | 002 | 2 | 1 | changed text2 | 2005-11-05
> *004 | 001 | 002 | 3 | 1 | changed text2 | 2005-11-15
> *005 | 001 | 003 | 1 | 1 | original text3 | 2005-11-20
> 006 | 002 | 001 | 1 | 1 | original transl1 | 2005-10-11
> *007 | 002 | 001 | 1 | 2 | changed transl1 | 2005-10-30
> 008 | 002 | 002 | 1 | 1 | original trans2 | 2005-11-02
>
> Table available_languages
> lang_id | approved
> 001 | 2005-10-10 (= default language)
> 002 | 2005-10-12
>
>
> Required results:
> The three 3 rows with astrixes in front of them should be returned:
> for item 001: content item 007 : the translation which has been approved
> and is of the same item version as the default
> for item 002: content item 004 : the changed default language text as the
> translation is of version 1 of the item and the default language is on
> version 3
> for item 003: content item 005 : no translation can be found, so return
> the default language
>
>
> The logic I am looking for is something along the lines of:
>
> SELECT tc.item_text
> FROM text_content as tc, text_items as item, available_languages as lang
> WHERE item.item_id = tc.item_id
> AND tc.lang_id = lang.lang_id
> AND item.approved <= CURDATE()
>
> AND IF FOUND (
> tc.lang_id = 002
> AND tc.approved <= CURDATE()
> AND lang.approved <= CURDATE()
> AND item.approved <= CURDATE()
> AND MAX(tc.translation_version)
> AND ( ( MAX(tc.item_version WHERE tc.lang_id = 001) - MAX(tc.item_version
> WHERE tc.lang_id = 002) ) < 2 )
> )
> ELSE (
> tc.lang_id = 001
> AND tc.approved <= CURDATE()
> AND lang.approved <= CURDATE()
> AND item.approved <= CURDATE()
> AND MAX(tc.translation_version)
> AND MAX(tc.item_version)
> )
>
>
> Until now I had a "left join to self" in place which returned a two column
> table with default language as column 1 and if available & all criteria
> fullfilled the translation in column 2.
>
> With php logic I would then test if column 2 was empty or not, if not,
> display the content of column2, else display the content of column 1.
>
>
> Any help would be much appreciated !
>
> Thanks in advance,
> Juliette
Navigation:
[Reply to this message]
|