Table Inner Join

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.

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 :wink:
Tire Auto chooser.fwin (69.1 KB)

Thanks Al, that worked perfectly. It does just what I need.

Well, at least we know how to pronounce aluminum. :slight_smile:

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:

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!

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

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:

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]

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.

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

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…

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:

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.

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

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,

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.