CODEWITHSUNDEEP
phpmysqlarrays
Don S
Don S

Reputation: 134

Can the query results form three different tables go into one array?

I am trying to pull all of the records in each of these tables that have a value in Games.date_added that is greater than an inputed date. Trying to pull all the new records.

Can I do that all in one query? Or is it easier to just add a date_added field to each table and query each table separately?

GAMES
id
date_added
game_name
release_date
game_category
game_type
game_console

TROPHIES
trophies_id
game_name
tr_name tr_description
tr_color
tr_ach_value

TROPHY_TOTALS
trophy_totals_id
game_name
bronze_ttl
silver_ttl
gold_ttl
plat_ttl
hidden_ttl

Upvotes: 0

Views: 94

Answers (2)

T.J.
T.J.

Reputation: 124

It will be much better and clearer if you do 3 separate queries. Each one will give you a different amount of rows. The query given by glglgl that uses 2 joins will result in multiplied rows from the games table. This is because tropies and tropy totals can contain multiple records with same game. Of course if game_name is a unique column than the join will be OK.

Upvotes: 1

glglgl
glglgl

Reputation: 91109

First of all, it would surely better to have your foreign key fields in TROPHIES and TROPHY_TOTALS be id resp. game_id rather than game_name.

This said, I will concentrate on your current table structure.

SELECT <wanted fields>
    FROM GAMES
    LEFT JOIN TROPHIES USING (game_name)
    LEFT JOIN TROPHY_TOTALS USING (game_name)
    WHERE date_added > <given>

Upvotes: 1

Related Questions