Simple join query?

Community Support
1

Content-Disposition: inline Hello,

I have the following data:

q)SECTOR

SC_ID SC_NAME
BM   “Basic Materials”
CG   “Capital Goods”
CO   “Conglomerates”
CC   “Consumer Cyclical”
CN   “Consumer Non-Cyclical”
EN   “Energy”
FN   “Financial”
HC   “Healthcare”
SV   “Services”
TC   “Technology”
TR   “Transportation”
UT   “Utilities”

q)meta SECTOR

c      t f a
SC_ID  s
SC_NAME C

q)I1 / has 102 rows

IN_ID IN_NAME                         IN_SC_ID
AA   “Apparel/Accessories”           CC
AC   “Air Courier”                   TR
AD   “Aerospace & Defense”           CG
AE   “Advertising”                   SV
AM   “Auto & Truck Manufacturers”    CC
AP   “Auto & Truck Parts”            CC
AR   “Airline”                       TR
AT   “Appliance & Tool”              CC
AV   “Audio & Video Equipment”       CC
BA   “Beverages (Alcoholic)”         CN
BC   “Broadcasting & Cable TV”       SV
BD   “Biotechnology & Drugs”         HC
BN   “Beverages (Non-Alcoholic)”     CN
BS   “Business Services”             SV
CA   “Constr. & Agric. Machinery”    CG
CC   “Chemicals - Plastics & Rubber” BM
CD   “Computer Storage Devices”      TC
CE   “Communications Equipment”      TC
CF   “Consumer Financial Services”   FN
CG   “Casinos & Gaming”              SV
..
q)meta I1
c       t f      a
-------- ----------
IN_ID   s
IN_NAME C
IN_SC_ID s SECTOR

I want to execute a SQL query of the following form
select IN_NAME from INDUSTRY,SECTOR where IN_SC_ID = SC_ID and SC_NAME like “Technology”

I tried
q)select IN_NAME from I1 where I1.SECTOR.SC_NAME = “Technology”
'I1 / I should get 11 rows

So, what is the right way to write this query?

Regards,
Yuva

2

There might be other ways to do it, but here is one:Given the following two CSV files:$ cat SECTOR.csvSC_ID,SC_NAMEBM,BasicMaterialsCG,CapitalGoodsCO,ConglomeratesCC,ConsumerCyclicalCN,ConsumerNon-Cyclical$ cat l1.csvIN_ID,IN_NAME,IN_SC_IDAA,Apparel/Accessories,BMAC,AirCourier,CGAD,Aerospace&Defense,COAE,Advertising,CCAM,Auto&TruckManufacturers,CNHere is some Q code that might be what you were after:q) SECTOR:SC_ID xkey ("S*"; enlist ",")0::SECTOR.csvq) l1:(“S*s”; enlist “,”)0::l1.csvq) l1[IN_SC_ID]: SECTOR$l1[IN_SC_ID]q) show select IN_NAME from l1 where SECTOR[( SC_ID:IN_SC_ID);`SC_NAME] like "BasicMaterials"IN_NAME---------------------"Apparel/Accessories"Note how we key into the SECTOR table by specifying the values fromthe IN_SC_ID column to be used as key:s on the foreign key columnSC_ID, and then provide the corresponding value of the SC_NAME column.I suggest reading the “Q for Mortals 2” on the code.kx.com wiki. Afterhaving read it, read it again.

3

Thanks.

Since we already have the relation defined, I could also do this: (using the original data I have) [after looking into "Q for Mortals 2" again ofcourse :)!]

q)select IN_NAME from l1 where IN_SC_ID.SC_NAME like "Basic Materials"
IN_NAME

"Chemicals - Plastics & Rubber"
"Containers & Packaging"
"Chemical Manufacturing"
"Fabricated Plastic & Rubber"
"Forestry & Wood Products"
"Gold & Silver"
"Iron & Steel"
"Misc. Fabricated Products"
"Metal Mining"
"Non-Metallic Mining"
"Paper & Paper Products"

which is what I was looking for.

Regards,
Yuva

p.s: I am getting a type error when I set foreign key with filled tables. I am currently working around that by defining an empty table with relations and then using upsert to load the data. How can I know more about the type error - where I have made the mistake?

4

On Mon, Dec 8, 2008 at 11:46 AM, Yuvaraj Athur Raghuvir<yuvaraj.a.r> wrote:> p.s: I am getting a type error when I set foreign key with filled tables. I&gt; am currently working around that by defining an empty table with relations&gt; and then using upsert to load the data. How can I know more about the type> error - where I have made the mistake?'type can come from lots of thingscan we see your session?</yuvaraj.a.r>

5

From my session:

q)meta s2

c      t f a
SC_ID  s
SC_NAME C
q)meta i2
c       t f a
-------- -----
IN_ID   s
IN_NAME C
IN_SC_ID s
q)select [10] from s2
SC_ID SC_NAME
----- -----------------------
BM   “Basic Materials”
CG   “Capital Goods”
CO   “Conglomerates”
CC   “Consumer Cyclical”
CN   “Consumer Non-Cyclical”
EN   “Energy”
FN   “Financial”
HC   “Healthcare”
SV   “Services”
TC   “Technology”
q)select [10] from i2
IN_ID IN_NAME                      IN_SC_ID
----- -------------------------------------
AA   “Apparel/Accessories”        CC
AC   “Air Courier”                TR
AD   “Aerospace & Defense”        CG
AE   “Advertising”                SV
AM   “Auto & Truck Manufacturers” CC
AP   “Auto & Truck Parts”         CC
AR   “Airline”                    TR
AT   “Appliance & Tool”           CC
AV   “Audio & Video Equipment”    CC
BA   “Beverages (Alcoholic)”      CN
q)i2[`IN_SC_ID]:`s2$i2[`IN_SC_ID]
'type
q)

What am I missing?

~Yuva

p.s: From the request to “see my session” I assume this is what I should provide: I execute commands on the q console and mark/copy/paste it into the mail. Is there anything else that is needed?

6

> q)i2[`IN_SC_ID]:`s2$i2[`IN_SC_ID]> 'typeyou can’t address keyed tables like thattryupdate`s2$IN_SC_ID from`i2

7

Thanks. This works fine.~YuvaOn 12/7/08, Aaron Davies <aaron.davies> wrote:>>> q)i2[`IN_SC_ID]:`s2$i2[`IN_SC_ID]>> 'type>> you can’t address keyed tables like that>> try>> update`s2$IN_SC_ID from`i2>> >>– Sent from Gmail for mobile | mobile.google.com</aaron.davies>