Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2017
    Posts
    2

    Lightbulb Unanswered: Qomplicated Query Question

    Hello All!

    Thanks for letting me pick some brains.

    About 12 years ago I got some great help (possibly here) to create this query:

    <code>
    SELECT * FROM (
    SELECT p1_fname AS player1, p1wr AS wr, p1lr AS lr, p1tr AS tr, p1wp AS wp,
    p1lp AS lp, p1tp AS tp, p1ws AS ws, p1ls AS ls, p1ts AS ts, p2_fname AS player2,
    game_id, game_date, p1_uname AS p1_un, p1_relationship AS p1_rel, p1_md, p1_as,
    p2_uname AS p2_un, p2_relationship AS p2_rel, p2_md, p2_as, type_game
    FROM games
    JOIN players ON players.username = games.p1_uname
    UNION
    SELECT p2_fname AS player1, p1ls AS wr, p1wp AS lr, p1tr AS tr, p1lr AS wp, p1ws AS lp,
    p1tp AS tp, p1lp AS ws, p1wr AS ls, p1ts AS ts, p1_fname AS player2,
    game_id, game_date, p2_uname AS p1_un, p2_relationship AS p1_rel, p2_md AS p1_md,
    p2_as AS p1_as, p1_uname AS p2_un, p1_relationship AS p2_rel, p1_md AS p2_md, p1_as AS p2_as, type_game
    FROM games
    JOIN players ON players.username = games.p1_uname
    ORDER BY game_id) AS q2;
    </code>

    What this query does is it transposes all the players into 'Player 1' and transposes the scores of the games played, so that each player can be searched for from either a player 1 or player 2 perspective. This query took me weeks to figure out (even with help), but it works perfectly. The only problem with it is that it doesn't draw all the fields from the players table that I need to search for on my search form. So, I started off and created the upper part of the query above, double drawing from both the games and players tables and got this:

    <code>
    SELECT p1_fname AS player1, p1wr AS wr, p1lr AS lr, p1tr AS tr, p1wp AS wp,
    p1lp AS lp, p1tp AS tp, p1ws AS ws, p1ls AS ls, p1ts AS ts, p2_fname AS player2,
    game_id, game_date, p1_uname AS p1_un, p1_relationship AS p1_rel, p1_md, p1_as,
    p2_uname AS p2_un, p2_relationship AS p2_rel, p2_md, p2_as, type_game, players.gender AS p1_gender,
    players.city AS p1_city, players.state_prov AS p1_state_prov, players.birth_date AS p1_birth_date,
    players.country AS p1_country, players.education AS p1_education, players.ethnicity AS p1_ethnicity,
    players.religion AS p1_religion, players.occupation AS p1_occupation, players.mate_status AS p1_mate_status
    FROM games, players
    WHERE players.username = games.p1_uname
    </code>

    This query above, by itself, works perfectly also, and gives me all the data I need. But I need to transpose the data in this query as the first one.

    So, I created the second half and tried to put it in the syntax of the first one, like so:

    <code>
    SELECT * FROM (
    SELECT p1_fname AS player1, p1wr AS wr, p1lr AS lr, p1tr AS tr, p1wp AS wp,
    p1lp AS lp, p1tp AS tp, p1ws AS ws, p1ls AS ls, p1ts AS ts, p2_fname AS player2,
    game_id, game_date, p1_uname AS p1_un, p1_relationship AS p1_rel, p1_md, p1_as,
    p2_uname AS p2_un, p2_relationship AS p2_rel, p2_md, p2_as, type_game, players.gender AS p1_gender,
    players.city AS p1_city, players.state_prov AS p1_state_prov, players.birth_date AS p1_birth_date,
    players.country AS p1_country, players.education AS p1_education, players.ethnicity AS p1_ethnicity,
    players.religion AS p1_religion, players.occupation AS p1_occupation, players.mate_status AS p1_mate_status
    FROM games, players
    WHERE players.username = games.p1_uname
    JOIN players ON players.username = games.p1_uname
    UNION
    SELECT p2_fname AS player1, p1ls AS wr, p1wp AS lr, p1tr AS tr, p1lr AS wp, p1ws AS lp,
    p1tp AS tp, p1lp AS ws, p1wr AS ls, p1ts AS ts, p1_fname AS player2, game_id, game_date,
    p2_uname AS p1_un, p2_relationship AS p1_rel, p2_md AS p1_md, p2_as AS p1_as, p1_uname AS p2_un,
    p1_relationship AS p2_rel, p1_md AS p2_md, p1_as AS p2_as, type_game AS p1_type_game,
    players.gender AS p1_gender, players.city AS p1_city, players.state_prov AS p1_state_prov,
    players.birth_date AS p1_birth_date, players.country AS p1_country, players.education AS p1_education,
    players.ethnicity AS p1_ethnicity, players.religion AS p1_religion, players.occupation AS p1_occupation,
    players.mate_status AS p1_mate_status, players.gender AS p1_gender
    FROM games, players
    JOIN players ON players.username = games.p2_uname
    ORDER BY game_id) AS q2;
    </code>

    But I get the error: "#1066 - Not unique table/alias: 'players' ". The query seems to work right up to the UNION directive, and seems to be offended by the 'players' table being mentioned in the WHERE clause just above it.

    I am a bit familiar with the SQL for MySQL, but I am not very good with the finer aspects. Does anyone see any problems, or have any ideas? As stated, the first transposition query works beautifully, and the second one drawing from 2 tables works as well, but the third query works not at all. Thanks in advance for any help.

    bhardin
    Last edited by bhardin; 01-28-17 at 18:09.

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Here is exactly where your error is coming from:

    Code:
    FROM games, players 
     JOIN players ON
    You are joining to your players table twice, once implicitly and then explicitly. If you want the table in there twice, then you must give each a different correlation name and use that name as the prefix for the columns of each.

  3. #3
    Join Date
    Jan 2017
    Posts
    2

    Problem solved

    Thank you for your reply DNance,

    While mulling over this problem, I remembered something about 'views', so I looked up some information on Views. I solved the problem by creating views of each games, players table for both Player 1 and Player 2. Once created, I could use each view as if it were a table, and was easily (almost) able to create another 'flip' query as I did on the first query example on my post. I am getting exactly the information I need now.

    So, I guess I did what you suggested and got unique names for the only two views involved, 'games_players_p1', and 'games_players_p2'. This is a neat trick I discovered years ago, but forgot about. Below is the final query that solved the problem:

    <code>

    SELECT * FROM (
    SELECT player1, wr, lr, tr, wp, lp, tp, ws, ls, ts, player2,
    game_id, game_date, p1_un, p1_rel, p1_md, p1_as,
    p2_un, p2_rel, p2_md, p2_as, type_game, p1_gender,
    p1_city, p1_state_prov, p1_birth_date,
    p1_country, p1_education, p1_ethnicity,
    p1_religion, p1_occupation, p1_mate_status
    FROM games_players_p1
    UNION
    SELECT player1, wr, lr, tr, wp, lp, tp, ws, ls, ts, player2,
    game_id, game_date, p1_un, p1_rel, p1_md, p1_as,
    p2_un, p2_rel, p2_md, p2_as, type_game, p1_gender,
    p1_city, p1_state_prov, p1_birth_date,
    p1_country, p1_education, p1_ethnicity,
    p1_religion, p1_occupation, p1_mate_status
    FROM games_players_p2
    ORDER BY game_id) AS q2;

    </code>

    Interesting query, don't you think?

    Thanks again, bhardin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •