# Simple join query? **URL:** \ **Category:** Community Support\ **Tags:** kdb-and-q\ **Created:** [December 4, 2008, 11:39pm UTC](https://forum.kx.com/t/simple-join-query/5859 "2008-12-04T23:39:00Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Yuvaraj\_Athur\_R](https://avatars.discourse-cdn.com/v4/letter/y/e8c25b/32.png) [@Yuvaraj\_Athur\_R](https://forum.kx.com/u/Yuvaraj_Athur_R)\ **Post date:** [December 4, 2008, 11:39pm UTC](https://forum.kx.com/t/simple-join-query/5859/1 "2008-12-04T23:39:00Z")
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 ---
**Author:** ![Lindqvist](https://avatars.discourse-cdn.com/v4/letter/l/b4bc9f/32.png) [@Lindqvist](https://forum.kx.com/u/Lindqvist)\ **Post date:** [December 7, 2008, 9:24pm UTC](https://forum.kx.com/t/simple-join-query/5859/2 "2008-12-07T21:24:00Z")
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](http://code.kx.com) wiki. Afterhaving read it, read it again. ---
**Author:** ![Yuvaraj\_Athur\_R](https://avatars.discourse-cdn.com/v4/letter/y/e8c25b/32.png) [@Yuvaraj\_Athur\_R](https://forum.kx.com/u/Yuvaraj_Athur_R)\ **Post date:** [December 8, 2008, 3:46am UTC](https://forum.kx.com/t/simple-join-query/5859/3 "2008-12-08T03:46:00Z")
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? ---
**Author:** ![Aaron\_Davies](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@Aaron\_Davies](https://forum.kx.com/u/Aaron_Davies)\ **Post date:** [December 8, 2008, 3:56am UTC](https://forum.kx.com/t/simple-join-query/5859/4 "2008-12-08T03:56:00Z")
On Mon, Dec 8, 2008 at 11:46 AM, Yuvaraj Athur Raghuvir\ wrote:\> 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?'type can come from lots of thingscan we see your session?\ ---
**Author:** ![Yuvaraj\_Athur\_R](https://avatars.discourse-cdn.com/v4/letter/y/e8c25b/32.png) [@Yuvaraj\_Athur\_R](https://forum.kx.com/u/Yuvaraj_Athur_R)\ **Post date:** [December 8, 2008, 4:56am UTC](https://forum.kx.com/t/simple-join-query/5859/5 "2008-12-08T04:56:00Z")
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? ---
**Author:** ![Aaron\_Davies](https://avatars.discourse-cdn.com/v4/letter/a/8e7dd6/32.png) [@Aaron\_Davies](https://forum.kx.com/u/Aaron_Davies)\ **Post date:** [December 8, 2008, 5:19am UTC](https://forum.kx.com/t/simple-join-query/5859/6 "2008-12-08T05:19:00Z")
\> q)i2[`IN_SC_ID]:`s2$i2[`IN_SC_ID]\> 'typeyou can’t address keyed tables like thattryupdate`s2$IN\_SC\_ID from`i2 ---
**Author:** ![Yuvaraj\_Athur\_R](https://avatars.discourse-cdn.com/v4/letter/y/e8c25b/32.png) [@Yuvaraj\_Athur\_R](https://forum.kx.com/u/Yuvaraj_Athur_R)\ **Post date:** [December 9, 2008, 3:56am UTC](https://forum.kx.com/t/simple-join-query/5859/7 "2008-12-09T03:56:00Z")
Thanks. This works fine.~YuvaOn 12/7/08, 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\