CODEWITHSUNDEEP
sqlsqlite
Michael Heffernan
Michael Heffernan

Reputation: 63

SQL Newbie - Unsure how to write query

I have 3 tables involved: Units Types Factions

Units has a foreign key from Types and Factions (TypeID and FactionID). I need to get Units.UnitName, using Types.TypeName and Factions.FactionName.

I think I may need a join, but I'm unsure how to structure it. Any help?

Edit: Here's a screenshot of the tables in question: https://i.sstatic.net/l4fVJ.png

I need to return this data: UnitName - SupplyCost - CreditsCost - MetalCost - CrystalCost

The form to get this information will provide FactionName and TypeName, as they are text values and therefore easily understandable by a user, as opposed to numeric IDs.

Upvotes: 1

Views: 83

Answers (4)

Gabriele Petrioli
Gabriele Petrioli

Reputation: 196236

SELECT
   SupplyCost, CreditsCost, MetalCost, CrystalCost
FROM
   Units 
      INNER JOIN Types ON Units.TypeID = Types.TypeId
      INNER JOIN Factions ON Units.FactionId = Factions.TypeId
WHERE 
      Types.TypeName = 'value from form for types'
      AND
      Factions.FactionName = 'value from form for factions'

You can read http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html for a nice and visual explanation of JOIN

Upvotes: 2

Shaikh Farooque
Shaikh Farooque

Reputation: 2640

Kindly check it

select UnitName, SupplyCost, CreditsCost, MetalCost, CrystalCost from Units
inner join Types on Types.TypeID = Units.TypeID 
inner join Factions on Faction.FactionID = Units.FactionID
WHERE Types.TypeName = @typeName
AND Factions.FactionName = @factionName

Upvotes: 2

amaters
amaters

Reputation: 2316

something like: 

select UnitName, SupplyCost, CreditsCost, MetalCost, CrystalCost from Units
inner join Types on Types.TypeID = Units.TypeID 
inner join Factions on Faction.FactionID = Units.FactionID
WHERE Types.TypeName = @typeName
AND Factions.FactionName = @factionName

Upvotes: 3

Miika L.
Miika L.

Reputation: 3353

SELECT Units.UnitName
FROM Units INNER JOIN Types ON Units.TypeId = Types.TypeId
    INNER JOIN Factions ON Units.FactionId = Factions.FactionId
WHERE Types.TypeName = @typeName
    AND Factions.FactionName = @factionName

Would that be what you are after? If not, you may need to provide more info in your question.

Upvotes: 3

Related Questions