#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Location
    NY
    Posts
    8
    Rep Power
    0

    Simplify the query to not use subquery


    Hello,
    I apologize in advance for long post

    Let's say I am running a monthly competition where individuals from different teams compete.
    So I set up 3 tables for information.
    1. tPlayer (playerid, teamid, name, etc...)
    2. tTeam (teamid, teamname, etc....)
    3. tComp (compid, compname, date, location, etc...)

    I also setup 1 table to define relationship of players with a given tournament
    4. rPlayerComp (playerid, compid, other info...)

    So if I wanted to find out all the players for a given competition it would be
    Code:
    SELECT * 
    FROM tPlayer inner join rPlayerComp on tPlayer.playerid = rPlayerComp.playerid
    WHERE rPlayerComp.compid = 'xxx'
    If I need to get competition detail or team information as well I can inner join tTeam and tComp. I hope you get the picture.

    Now here is the question. I need to make a query so that I can see all individuals registered for a competition PLUS all the teams with no players signed up.

    I can get it done by using this query .
    Code:
    SELECT tPlayer.name, tTeam.name
    FROM ( 
         SELECT * 
         FROM tPlayer inner join rPlayerComp on tPlayer.playerid = rPlayerComp.playerid 
         WHERE rPlayerComp.compid = 'xxx') sqr 
    RIGHT OUTER JOIN tTeam on sqr.teamid = tTeam.teamid
    Basically I have a subquery get all the registered player for the competition (I name it sqr) and right join to the list of teams which will return all registered players and also the teams wit no player registered (the name field would be null)

    Here is where I need help.
    A front end program I am using doesn't like subqueries. I think it sees the second select and throws up error since it thinks there should only be 1 select in a query.

    Is there a way to make the query above without using the subquery?

    Thank you in advance.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,969
    Rep Power
    374
    what is the front end program that you are using?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Posts
    10
    Rep Power
    0

    Create a View?


    Does the front end application play friendlier with views?

    If this sub-query is something that you use frequently you may want to consider making it a view anyway.

IMN logo majestic logo threadwatch logo seochat tools logo