# Table Inner Join **URL:** \ **Category:** Archived Topics\ **Created:** [October 1, 2008, 3:43am UTC](https://forum.inductiveautomation.com/t/table-inner-join/830 "2008-10-01T03:43:01Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\ **Post date:** [October 1, 2008, 3:43am UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/1 "2008-10-01T03:43:01Z")
I am having a problem with building a sql query to populate a table. You guys will probably come up with a solution in under 60 seconds. Suppose we have three tables: Table1, Table2, Table3. In Table1, the columns are Auto\_ndx, and Autos. In Table2, the columns are Tire\_ID and Auto\_ID. In Table3, the columns are Tire\_ndx and TireType. In this example, Auto\_ID = Auto\_ndx, and Tire\_ID = Tire\_ndx Table2 contains a list of every possible tire-auto combination, i.e: 1, 5 1, 8 1, 23 2, 11 2, 8, 2, 23 etc. So, I have made a FPMI table, and I want the columns to represent the actual tire brand and auto model instead of the IDs. I can get stand-alone inner joins to work fine, but I can’t get everything to work at the same time. The resulting data should look like Goodyear, Ford F150 Goodyear, Ford F250 Goodyear, Ford Escape Bridgestone, Mercury Mountaineer Bridgestone, Ford F250 Bridgestone, Ford Escape meaning that Goodyear is Tire\_ndx ‘1’ and Bridgestone is Tire\_ndx ‘2’, Ford F150 is Auto\_ndx ‘5’, etc. You get the idea. I suppose I could add some columns and update Table2 periodically with a join, but that sort of defeats the purpose of a relational database. And, the whole point will be for my customer to be able to add rows to Table2 symbolically by selecting from a list of autos and tires from a couple of dropdown lists. See where I’m going with this? Any ideas are appreciated as always. ---
**Author:** ![AlThePal](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/althepal/32/3603_2.png) [@AlThePal](https://forum.inductiveautomation.com/u/AlThePal)\ **Post date:** [October 1, 2008, 9:37am UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/2 "2008-10-01T09:37:59Z")
Hi Step7, The following query should do what you want: `SELECT Table3.TireType, Table1.Autos FROM Table2 LEFT JOIN Table3 ON Table2.Tire_ID=Table3.Tire_ndx LEFT JOIN Table1 ON Table2.Auto_ID=Table1.Auto_ndx` I’ve put this together in a window with code to let a user select new combinations using a couple of dropdown lists as you were proposing. The only other problem I can see is that you keep spelling ‘tyre’ wrongly 😉 [Tire Auto chooser.fwin](https://us1.discourse-cdn.com/inductiveautomation/original/1X/acbab888b3ea4ce06b89ced85b9e91fed82700b6.fwin) (69.1 KB) ---
**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\ **Post date:** [October 1, 2008, 2:37pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/3 "2008-10-01T14:37:16Z")
Thanks Al, that worked perfectly. It does just what I need. > [@AlThePal](#): > > The only other problem I can see is that you keep spelling 'tyre' wrongly 😉 Well, at least we know how to pronounce aluminum. 🙂 ---
**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\ **Post date:** [March 29, 2009, 6:45pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/4 "2009-03-29T18:45:44Z")
Ok, another SQL query question that’s has me stumped (SQL is definitely my weak spot, but I’m getting better). Let’s say I have three tables- “Truck”, “Crate”, and “Appliance”. “Truck” and “Crate” have “TruckID” columns, and “Crate” and “Appliance” have “CrateID” columns. The “Appliance” table also has a status column, with 1=present, and 0=unloaded (i.e., the truck could start out with three crates of ten appliances each, and the status is set to 0 as each appliance is unloaded) What I want to do is write a query that displays the number of appliances in each truck. Here is what I’ve got so far: ```auto SELECT c.TruckID, a.Status, (SELECT COUNT(a.Status) AS Expr1 FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID WHERE a.Status = 1) AS Total FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID INNER JOIN Truck AS t ON c.TruckID = t.TruckId GROUP BY c.TruckID, a.Status ``` My truck table has five trucks, and the query correctly generates five rows, one for each truck. But the Total column in each row is the total of all appliances still in all trucks. No matter what I try, I can’t seem to get a unique count per truck, but I know I’m missing something simple. Another set of eyes would help. Thanks! ---
**Author:** ![mrtweaver](https://avatars.discourse-cdn.com/v4/letter/m/77aa72/32.png) [@mrtweaver](https://forum.inductiveautomation.com/u/mrtweaver)\ **Post date:** [March 30, 2009, 1:12am UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/5 "2009-03-30T01:12:48Z")
What I would recommend, and I am not sure it will do what you want but it is worth a try. In your query you have > [@](#): > > (SELECT COUNT(a.Status) AS Expr1 what I would try is change the word COUNT to SUM. I am using something similar and count counted the rows regardless of the value, where sum only added the numbers together. Hope this helps. [quote="Step7"]Ok, another SQL query question that's has me stumped (SQL is definitely my weak spot, but I'm getting better). Let's say I have three tables- "Truck", "Crate", and "Appliance". "Truck" and "Crate" have "TruckID" columns, and "Crate" and "Appliance" have "CrateID" columns. The "Appliance" table also has a status column, with 1=present, and 0=unloaded (i.e., the truck could start out with three crates of ten appliances each, and the status is set to 0 as each appliance is unloaded) What I want to do is write a query that displays the number of appliances in each truck. Here is what I've got so far: ```auto SELECT c.TruckID, a.Status, (SELECT COUNT(a.Status) AS Expr1 FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID WHERE a.Status = 1) AS Total FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID INNER JOIN Truck AS t ON c.TruckID = t.TruckId GROUP BY c.TruckID, a.Status ``` My truck table has five trucks, and the query correctly generates five rows, one for each truck. But the Total column in each row is the total of all appliances still in all trucks. No matter what I try, I can't seem to get a unique count per truck, but I know I'm missing something simple. Another set of eyes would help. Thanks![/quote] ---
**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\ **Post date:** [March 30, 2009, 2:36pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/6 "2009-03-30T14:36:27Z")
I can’t use SUM, because the status will not always be 0 or 1, or I may want to count records based on some other non-int criteria. I know the problem is in my COUNT join, but can’t seem to find a way to count only records belonging to a certain group and meeting certain criteria. I played around with CTEs, and it made the query a little simpler, and it now even comes up with unique counts for each group, but still fails when I add a conditional to detect only the status of 1. ```auto WITH cte_t AS (SELECT c.TruckID, a.Status,COUNT(*) AS Total FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID INNER JOIN Truck AS t ON c.TruckID = t.TruckId GROUP BY c.TruckID, a.Status) SELECT * FROM cte_t ``` ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 30, 2009, 3:19pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/7 "2009-03-30T15:19:41Z")
I’m not sure I understand the whole picture here, but I think you’re confusing matters with the subqueries. Try: `SELECT c.TruckID, COUNT(*) FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID INNER JOIN Truck AS t ON c.TruckID = t.TruckId WHERE a.Status=1 GROUP BY c.TruckID, a.Status` or maybe `SELECT c.TruckID, SUM(CASE WHEN a.Status=1 THEN 1 ELSE 0 END) FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID INNER JOIN Truck AS t ON c.TruckID = t.TruckId GROUP BY c.TruckID, a.Status` I think those should be equivalent… ---
**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\ **Post date:** [March 30, 2009, 3:31pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/8 "2009-03-30T15:31:32Z")
Yes, I tried something like that before, but it doesn’t return a row where there are zero appliances in the truck. For instance, let’s say Truck\_1 has 0 appliances, and Truck\_2 has 10 appliances. What I would like to return as a result is something like this: ```auto TRUCK TOTAL 1 0 2 10 ``` But, when I use “where status = 1”, I don’t display a row for trucks that have zero appliances. ---
**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\ **Post date:** [March 30, 2009, 4:35pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/9 "2009-03-30T16:35:22Z")
Right, try a left join then: `SELECT t.TruckID, SUM(CASE WHEN a.Status=1 THEN 1 ELSE 0 END) FROM Truck AS t LEFT JOIN Crate as c ON c.TruckID = t.TruckId LEFT JOIN Appliance AS a ON a.CrateID = c.CrateID GROUP BY t.TruckID` ---
**Author:** ![Colby.Clegg](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/colby.clegg/32/3282_2.png) [@Colby.Clegg](https://forum.inductiveautomation.com/u/Colby.Clegg)\ **Post date:** [March 30, 2009, 4:42pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/10 "2009-03-30T16:42:47Z")
This seemed to work for me… the right join seems to be what you want (taken from some website: “Right join: Return all rows from the right table, even if there are no matches in the left table”. Note: This was based on Carl G.'s query above. However, I also changed the table be reference for the TruckId (now t.TruckId instead of c). `SELECT t.TruckID, SUM(CASE WHEN a.Status=1 THEN 1 ELSE 0 END) FROM Appliance AS a INNER JOIN Crate AS c ON a.CrateID = c.CrateID RIGHT JOIN Truck AS t ON c.TruckID = t.TruckId GROUP BY t.TruckID, a.Status;` Edit: I see Carl post about left joins- it’s really the same thing, just depends on whose on the left and whose on the right. In his example, he switched the tables so that Truck was on the left. I left Truck on the right, and switched the join type. Regards, ---
**Author:** ![Step7](https://avatars.discourse-cdn.com/v4/letter/s/45deac/32.png) [@Step7](https://forum.inductiveautomation.com/u/Step7)\ **Post date:** [March 30, 2009, 4:49pm UTC](https://forum.inductiveautomation.com/t/table-inner-join/830/11 "2009-03-30T16:49:59Z")
Cool. It works with the case statement, but not count, but I like case better. I never thought about using case like this, but I can see that I can keep a running total based on any criteria now. Very nice. Thanks again.