By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,163 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Display data from more than 1 table without using join?

P: 4
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
Share this Question
Share on Google+
4 Replies


100+
P: 144
Hai marvStyles..try this...

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

OR

Select p.PlayerID,p.PlayerName,t.TeamID,t.TeamName from tblCompPlayers p,tblTeams t
Nov 25 '08 #2

amitpatel66
Expert 100+
P: 2,367
If you dont use JOINS then how you expect to get correct results?
Nov 25 '08 #3

P: 18
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

100+
P: 152
gpl
@Marvstyles
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
  11.  
  12.  
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
  11.  
  12.  
This assumes a player will only play for 1 team
[/edit]

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

Post your reply

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