How to join multiple fields to one table.

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

SQL Server Development (2000)

How to join multiple fields to one table.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-22 : 09:11:11

Lawrence Kerr writes "I have a table, which contains various types of information on circuits. Two pieces of the information are the ASite and BSite - where the circuit begins and where it ends. Each site contains various information such as address etc.

I have written a query, which returns all the data fields that I require. The problem is when I try to select specific data I cant reference the fields data from these two fields.

eg

Original Query:
(Note a simple join will not work, as I can't reference the field names to display the data)
select circuitID, ...other fields
(
select streetAddress from tblsites
where SiteId = tblCircuits.AEndLookUpSiteID
) as AName,
(
select streetAddress from tblsites
where SiteId = tblCircuits.AEndLookUpSiteID
) as BName
from tblCircuits;
This returns all data and I can display the data by response.write(RS("AName")) and so forth.

Searching :
This is the problem, the alias doesn’t exist until the query is run so if I say - WHERE AName = searchStr OR BName = searchStr the server returns an error stating invalid column names.

I cant use tblSites.StreetAddress as there are two instances - one for AEnd and one for BEnd. Is there anyway round this?"

Jay99

468 Posts

Posted - 2002-03-22 : 09:32:19


select
    circuitID,
    ...other fields,
    a.streetAddress as AName,
    b.streetAddress as BName
from
    tblCircuits c
    left join tblsites a --left join incase there is no address, your call
        c.AEndLookUpSiteID = a.SiteID
    left join tblsites b
        c.BEndLookUpSiteID = b.SiteID --I assume you ment B here
where
    a.streetaddress like '%47th Street%' -- or whatever

Jay
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources