How to combine two tables, one with 1 row and one with n rows?

Question

I have a database with two tables

One with games and one with participants A game is able to have more participants and these are in a different table.

Is there a way to combine these two into one query?

Thanks

Answer 1

You can combine them using the JOIN operator.

Something like

SELECT *
FROM   games g
       INNER JOIN participants p ON p.gameid = g.gameid

Explanation on JOIN operators

INNER JOIN - Match rows between the two tables specified in the INNER JOIN statement based on one or more columns having matching data. Preferably the join is based on referential integrity enforcing the relationship between the tables to ensure data integrity. o Just to add a little commentary to the basic definitions above, in general the INNER JOIN option is considered to be the most common join needed in applications and/or queries. Although that is the case in some environments, it is really dependent on the database design, referential integrity and data needed for the application. As such, please take the time to understand the data being requested then select the proper join option. o Although most join logic is based on matching values between the two columns specified, it is possible to also include logic using greater than, less than, not equals, etc.

LEFT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the left table. On the right table, the matching data is returned in addition to NULL values where a record exists in the left table, but not in the right table. o Another item to keep in mind is that the LEFT and RIGHT OUTER JOIN logic is opposite of one another. So you can change either the order of the tables in the specific join statement or change the JOIN from left to right or vice versa and get the same results.

RIGHT OUTER JOIN - Based on the two tables specified in the join clause, all data is returned from the right table. On the left table, the matching data is returned in addition to NULL values where a record exists in the right table but not in the left table.

Self -Join - In this circumstance, the same table is specified twice with two different aliases in order to match the data within the same table.

CROSS JOIN - Based on the two tables specified in the join clause, a Cartesian product is created if a WHERE clause does filter the rows. The size of the Cartesian product is based on multiplying the number of rows from the left table by the number of rows in the right table. Please heed caution when using a CROSS JOIN.

FULL JOIN - Based on the two tables specified in the join clause, all data is returned from both tables regardless of matching data.

Answer 2

example

table Game has columns (gameName, gameID)
table Participant has columns (participantID, participantName, gameID)

the GameID column is the "link" between the 2 tables. you need a common column you can join between 2 tables.

SELECT gameName, participantName
FROM Game g
JOIN Participat p ON g.gameID = p.gameID

This will return a data set of all games and the participants for those games. The list of games will be redundant unless you structure it some other way due to multiple participants to that game.

sample data

WOW        Bob
WOW        Jake
StarCraft2 Neal
Warcraft3  James
Warcraft3  Rich
Diablo     Chris