Display data from more than 1 table without using join?

[Marvstyles]

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.

[davidson1]

Try This

Hai marvStyles..try this...

Select tblCompPlayers. PlayerID,tblCom pPlayers.Player Name,tblTeams.T eamID,tblTeams. TeamName from tblCompPlayers, tblTeams

OR

Select p.PlayerID,p.Pl ayerName,t.Team ID,t.TeamName from tblCompPlayers p,tblTeams t

[amitpatel66]

If you dont use JOINS then how you expect to get correct results?

[garyb2008]

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?

[gpl]

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.

It sounds like you want a correlated subquery

Select 
       T.Teamid,
       T.Teamname,
       (Select Playerid
        From   Tblcompplayers P
        Where  P.Teamid = T.Teamid) As Playerid,
       (Select Playername
        From   Tblcompplayers P
        Where  P.Teamid = T.Teamid) As Playername
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

Select
       P.Playerid,
       P.Playername,
       (Select T.Teamid
        From   Tblteams T
        Where  P.Teamid = T.Teamid) As Teamid,
       (Select T.Teamname
        From   Tblteams T
        Where  P.Teamid = T.Teamid) As Teamname
From   Tblcompplayers P

This assumes a player will only play for 1 team
[/edit]

If this is homework then ask yourself 'have I learned anything?'
Graham