left join when... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

left join when...

Author

Topic

EveNut
Starting Member

5 Posts

Posted - 2007-04-08 : 15:18:19

I am trying to create a query that will display standard information from several joined tables reguardless of who executes it. At the same time, I would like to display unique information for each user within the same query, when it exists.

I would like to do this without limiting any rows displayed just simply add the additional information based on the user.

Here is what I have:

select chrFactions.factionName'Faction',chrFactions.factionID'FactionID', mapRegions.regionName'Region',mapRegions.RegionID'RegionID',
en1.itemName'Agent',crpNPCDivisions.divisionName'Divison',agtAgents.agentTypeID,
en2.ItemName'Corporation',staStations.stationName'Station',
Round(mapSolarsystems.security,1,2)'Security',agtAgents.level,
agtAgents.agentID'AgentID', agtAgents.quality'Quality',sum(((agtagents.[level]*2)-2)+agtagents.quality*0.05)'Required Standing',
MapSolarsystems.solarsystemname'System',staStations.corporationid'CorpID',
mapSolarsystems.SolarsystemID'SystemID',stastations.stationID,
playeragent.loyaltyPoints'LP', playeragent.favoriteAgent'Favorite'
from agtAgents inner join eveNames en1 on en1.itemId = agtAgents.agentID
inner join evenames en2 on en2.groupid = 2
inner join staStations on stastations.stationID = agtAgents.stationID
and en2.ItemId = staStations.corporationid
inner join mapSolarsystems on mapSolarsystems.solarsystemID = staStations.solarsystemID
inner join crpNPCDivisions on crpNPCDivisions.divisionID = agtAgents.divisionID
inner join crpNPCCorporations on crpNPCCorporations.corporationID = staStations.corporationID
inner join chrFactions on chrFactions.factionID = crpNPCCorporations.factionID
inner join mapRegions on mapRegions.regionID = mapSolarSystems.regionID
left join playeragent on agtAgents.agentID = playeragent.agentID
where mapSolarsystems.solarsystemID = 30001729
--and playeragent.playerid = @PlayerID
group by agtagents.quality, agtagents.[level], agtagents.agentid,chrFactions.factionName,
mapRegions.regionName,en1.itemName,crpNPCDivisions.divisionName,agtAgents.agentTypeID,
en2.itemName,staStations.stationName,mapSolarsystems.security,mapSolarsystems.solarSystemName,
staStations.corporationID,mapSolarsystems.solarSystemID,staStations.stationID,chrFactions.factionID,
mapRegions.regionID,playeragent.loyaltyPoints,playeragent.favoriteAgent order by Corporation asc, level desc, quality desc

Returned Rows:
Faction,FactionID,Region,RegionID,Agent,Division,TypeID,Corporation,Station,Security,level,agentID,quality,required,system,CorpID,systemID,stationID,LP,Favorite

The two columns in RED are the columns I would like to be able to display based on the PlayerID.

The PlayerAgent Table has the following columns:

PlayerID(PK), AgentID(PK), LoyaltyPoints, FavoriteAgent

I do not want to limit the rows based on the PlayerID,... instead, I would like to show information from the PlayerAgent table WHEN it exists for that player / Agent combo, otherwise, return NULL in the LP and FavoriteAgent columns.

My attempts to use a subquery have resulted in limiting the rows returned based on values in the PlayerAgent Table. Perhaps someone could point me in the right direction to find the correct method to return the desired results.

Thanks in advance.

EveNut

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2007-04-08 : 17:28:20

Move the condition from the WHERE clause to the JOIN clause:

select chrFactions.factionName'Faction',chrFactions.factionID'FactionID', mapRegions.regionName'Region',mapRegions.RegionID'RegionID',
en1.itemName'Agent',crpNPCDivisions.divisionName'Divison',agtAgents.agentTypeID,
en2.ItemName'Corporation',staStations.stationName'Station',
Round(mapSolarsystems.security,1,2)'Security',agtAgents.level,
agtAgents.agentID'AgentID', agtAgents.quality'Quality',sum(((agtagents.[level]*2)-2)+agtagents.quality*0.05)'Required Standing',
MapSolarsystems.solarsystemname'System',staStations.corporationid'CorpID',
mapSolarsystems.SolarsystemID'SystemID',stastations.stationID,
playeragent.loyaltyPoints'LP', playeragent.favoriteAgent'Favorite'
from agtAgents inner join eveNames en1 on en1.itemId = agtAgents.agentID
inner join evenames en2 on en2.groupid = 2
inner join staStations on stastations.stationID = agtAgents.stationID
and en2.ItemId = staStations.corporationid
inner join mapSolarsystems on mapSolarsystems.solarsystemID = staStations.solarsystemID
inner join crpNPCDivisions on crpNPCDivisions.divisionID = agtAgents.divisionID
inner join crpNPCCorporations on crpNPCCorporations.corporationID = staStations.corporationID
inner join chrFactions on chrFactions.factionID = crpNPCCorporations.factionID
inner join mapRegions on mapRegions.regionID = mapSolarSystems.regionID
left join playeragent on agtAgents.agentID = playeragent.agentID and playeragent.playerid = @PlayerID
where mapSolarsystems.solarsystemID = 30001729
group by agtagents.quality, agtagents.[level], agtagents.agentid,chrFactions.factionName,
mapRegions.regionName,en1.itemName,crpNPCDivisions.divisionName,agtAgents.agentTypeID,
en2.itemName,staStations.stationName,mapSolarsystems.security,mapSolarsystems.solarSystemName,
staStations.corporationID,mapSolarsystems.solarSystemID,staStations.stationID,chrFactions.factionID,
mapRegions.regionID,playeragent.loyaltyPoints,playeragent.favoriteAgent order by Corporation asc, level desc, quality desc

rockmoose

EveNut
Starting Member

5 Posts

Posted - 2007-04-08 : 20:16:00

oh >.<

Thank you rockmoose.

I feel rather sheepish now that I look at this.

EveNut

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources