Thanks, again, for folks who suggested solutions to my problem. To help
users searching the archives, I've pasted in a working solution at the
end of this message. Also, I'd like to ask if there is a more efficient
or better way of checking if the language version exist than the six
lines I repeated 8 times below.
Thanks, again.
-Kevin
=================================================\
I originally wrote:
I have a database of publications in different languages. "main
categories" are organized into "sub categories" with "baseitems" of
publications. Each baseitem can be printed in one or more of eight
languages. My SQL query so far is:
<snip>
====================================================
Working solution:
[EMAIL PROTECTED]:~$ cat OrderDB-requested.sql
SELECT
m.title AS "Main Category",
s.title AS "Sub Category",
b.partno AS "Part Number",
(SELECT lv.title
FROM langversion AS lv
WHERE lv.langid = "1" # English = 1
AND b.baseitemid = lv.baseitemid
) as "English Title",
CONCAT(
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "1" # 1 = English
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'E', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "2" # 2 = French
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'F', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "3" # 3 = Spanish
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'S', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "4" # 4 = Portuguese
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'P', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "5" # 5 = Arabic
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'A', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "6" # 6 = Swahili
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'W', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "7" # 7 = Russian
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'R', '-'),
IF(EXISTS (SELECT *
FROM langversion AS lv
WHERE lv.langid = "8" # 8 = Turkish
AND b.baseitemid = lv.baseitemid
AND lv.available = 'Y'
), 'T', '-')
)AS "Lang Avail"
FROM maincategory AS m
JOIN subcategory AS s ON m.maincatid=s.maincatid
JOIN baseitem AS b ON s.subcatid=b.subcatid
WHERE
b.available = "Y"
ORDER BY m.title, s.title;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
