List:General Discussion« Previous MessageNext Message »
From:Kevin Zembower Date:September 28 2006 3:39pm
Subject:RE: Requesting help with subquery
View as plain text  
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:
kevinz@www:~$ 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;

Thread
Requesting help with subqueryKevin Zembower26 Sep
  • Re: Requesting help with subqueryJohan Höök26 Sep
Re: Requesting help with subqueryChris Sansom26 Sep
RE: Requesting help with subqueryKevin Zembower26 Sep
RE: Requesting help with subqueryKevin Zembower28 Sep