CODEWITHSUNDEEP
phpmysqlsql
ncf
ncf

Reputation: 556

Check if the ID of one table is used as a foreign key in another

I have a mysql database that has two tables:

GAME (ID, Name)

TICKET (ID, GameID, Section...)

Game.ID is a primary key in GAME, and GameID is used in the TICKET table as a foreign key to know which game it is part of.

I have the sql statement:

SELECT * FROM GAME ORDER BY Date

It returns all game records from the GAME table. I want a sql statement that will only return the game record if there is at least 1 record using it as a foreign key in the TICKET table, thus only giving me the games that have tickets.

I've tried joining the tables together and checking it that way, but my sql just isn't there yet.

Thanks in advance for any help you can give me.

Upvotes: 2

Views: 1940

Answers (4)

Rottingham
Rottingham

Reputation: 2604

SELECT * FROM GAME
JOIN TICKET ON GAME.ID = TICKET.GameID
GROUP BY Game.ID
ORDER BY Date

Upvotes: 2

planestepper
planestepper

Reputation: 3307

Let's get fancy

SELECT * FROM GAME G
WHERE EXISTS
    (
    SELECT 1
    FROM TICKET T
    WHERE G.ID = T.GameID
    )

Upvotes: 2

Twelfth
Twelfth

Reputation: 7180

select * from game g inner join ticket t on g.id = t.game_id

That will return one row per ticket (5 tickets to one game will become 5 rows here)...uses an 'inner join' as a filter.

I suspect you are looking for something more like

Select * from game where id in (select game_id from ticket group by game_id)

This will give you one row per game regardless of number of tickets.

Upvotes: 2

undone
undone

Reputation: 7888

SELECT * FROM GAME ,TICKET   where TICKET.GameID  = GAME.ID ORDER BY Date

Upvotes: 0

Related Questions