468,316 Members | 1,925 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Display data from more than 1 table without using join?

How would i make a query that displays fields from 2 different tables without using JOIN?

I need a query that shows PlayerID and PlayerName from the table tblCompPlayers along with TeamID and TeamName from the table tblTeams. I am not allowed to use JOIN.
Nov 25 '08 #1
4 2143
144 100+
Hai marvStyles..try this...

Select tblCompPlayers.PlayerID,tblCompPlayers.PlayerName, tblTeams.TeamID,tblTeams.TeamName from tblCompPlayers,tblTeams


Select p.PlayerID,p.PlayerName,t.TeamID,t.TeamName from tblCompPlayers p,tblTeams t
Nov 25 '08 #2
2,367 Expert 2GB
If you dont use JOINS then how you expect to get correct results?
Nov 25 '08 #3
Im quite new to this as weel, as far as im aware you can get the same effect as using JOIN by using a WHERE clause, in fact using the where clause is reffered to as the old style of joining tables.

The syntax is as follows.....

ANSI Style
select * from a join b on a.id = b.id

Old Style
select * from a, b where a.id = b.id

Is this what your getting at?
Nov 25 '08 #4
152 100+
It sounds like you want a correlated subquery

Expand|Select|Wrap|Line Numbers
  1. Select 
  2.        T.Teamid,
  3.        T.Teamname,
  4.        (Select Playerid
  5.         From   Tblcompplayers P
  6.         Where  P.Teamid = T.Teamid) As Playerid,
  7.        (Select Playername
  8.         From   Tblcompplayers P
  9.         Where  P.Teamid = T.Teamid) As Playername
  10. From   Tblteams T
Not a join in sight, but it is a pseudo join. This is not very efficient as the selectneeds to be done for each row on the Team table, the query optimiser cannot do much to help.

[edit] - I just realised, this is all wrong, the subquery will return all the players for the team, try this
Expand|Select|Wrap|Line Numbers
  1. Select
  2.        P.Playerid,
  3.        P.Playername,
  4.        (Select T.Teamid
  5.         From   Tblteams T
  6.         Where  P.Teamid = T.Teamid) As Teamid,
  7.        (Select T.Teamname
  8.         From   Tblteams T
  9.         Where  P.Teamid = T.Teamid) As Teamname
  10. From   Tblcompplayers P
This assumes a player will only play for 1 team

If this is homework then ask yourself 'have I learned anything?'
Nov 26 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Ben R. | last post: by
18 posts views Thread by Terry Holland | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.