[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Sql 2000 linking an id in 1 table to 2 different columns in another table

Posted on 2011-10-25
4
Medium Priority
?
195 Views
Last Modified: 2012-08-02
How do i do a select statement using 2 tables where I want to link the orgsiteid to BOTH
the orgtoplevel and orgid in the 2 example tables below.
the result Im after is in the 3rd table.

hope this is clear!

NESTED Query where table org has got a parent child relationship
how to show all of the columns for parent name and childname in the result on a lookup
RESULT I want to achieve to show the parentname for the orgsiteid as well as its orgtoplevel name for the orgsiteid
I believe there is a way to get this?


select * from orgsite
inner join org on orgsite.orgid=org.orgid


Table = OrgSite

Orgsiteid      orgid                               
4000            1                              
4001            3                              
4002            4                              
4003            5                              
4006            1                              
                                    
Table=Org

Orgid      Orgname      OrgParent      Orgtoplevel                  
1      test1            1                  
2      test2            2                  
3      test3            2      2                  
4      test4            2      2                  
5      test5            5                  
                                    
                                    
RESULT I want to achieve to show the parentname for the orgsiteid as well as its orgtoplevel name for the orgsiteid
I believe there is a way to get this?


Orgsiteid      Orgid      Orgparent      Orgtoplevel      OrgName      OrgParentName      OrgNameTopLevel
4000      1      1      1      test1      test1      test1
4001      3      3      2      test3      TEST2      TEST2
4002      4      4      2      test4      TEST2      TEST2
4003      5      5      5      test5      test5      test5
4006      1      1      1      test1      test1      test1
0
Comment
Question by:Chris Michalczuk
  • 2
4 Comments
 
LVL 15

Assisted Solution

by:Eyal
Eyal earned 750 total points
ID: 37029409
try this...

select * from orgsite
inner join org org1 on orgsite.orgid=org1.orgid
left join org org2 on org2.orgid=org1.Orgparent 
left join org org3 on org3.orgid=org1.Orgtoplevel                  

Open in new window

0
 
LVL 15

Accepted Solution

by:
AmmarR earned 750 total points
ID: 37029474
hi dear

am unable to get your logic but the code below gets the result you wanted


select 
orgsite.Orgsiteid, 
orgsite.orgid, 
org.Orgid as 'Orgparent',
org.OrgParent 'Orgtoplevel', 
org.Orgname
,(Select top 1 o1.Orgname from #Org o1 where o1.OrgParent =org.OrgParent ) as 'OrgParentName'
,(Select top 1 o1.Orgname from #Org o1 where o1.OrgParent =org.OrgParent ) as 'OrgNameTopLevel'
from #orgsite orgsite 
inner join #Org org on org.Orgid =orgsite.orgid
order by orgsite.Orgsiteid

Open in new window

0
 

Author Comment

by:Chris Michalczuk
ID: 37029629
I tried to follow logic on both this but errors  as follows

Error in WHERE clause near 'FROM'.
Missing FROM clause.
Unable to parse query text.

SELECT    
tbl_OrganisationSitesLinks.OrganisationSitesLinks_SiteID,
tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID,
Tbl_Organisations.Organisations_ID AS 'Orgparent',
tbl_Organisations.Organisations_ID AS 'Orgtoplevel',
tbl_Organisations.Organisations_Name AS 'OrgName',

                          (SELECT     TOP 1 o1.Organisations_Name
                            FROM       tbl_Organisations o1
                            WHERE      o1.organisations_parentid = organisations_parentid) AS 'ORGParentName',

                          (SELECT     TOP 1 o1.Organisations_Name
                            FROM          tbl_Organisations o1
                            WHERE              o1.organisations_parentid = organisations_parentid) AS 'ORGTopLevel',

FROM         tbl_OrganisationSitesLinks

 INNER JOIN
                      tbl_Organisations org ON org.organisations_id = tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID

0
 
LVL 15

Expert Comment

by:AmmarR
ID: 37029787
remove the last  comma before the FROM


SELECT     
tbl_OrganisationSitesLinks.OrganisationSitesLinks_SiteID, 
tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID, 
Tbl_Organisations.Organisations_ID AS 'Orgparent', 
tbl_Organisations.Organisations_ID AS 'Orgtoplevel', 
tbl_Organisations.Organisations_Name AS 'OrgName',

      (SELECT     TOP 1 o1.Organisations_Name
        FROM       tbl_Organisations o1
        WHERE      o1.organisations_parentid = organisations_parentid) AS 'ORGParentName',

      (SELECT     TOP 1 o1.Organisations_Name
        FROM          tbl_Organisations o1
        WHERE              o1.organisations_parentid = organisations_parentid) AS 'ORGTopLevel'

FROM         tbl_OrganisationSitesLinks

INNER JOIN
  tbl_Organisations org ON org.organisations_id = tbl_OrganisationSitesLinks.OrganisationSitesLinks_OrganisationID

Open in new window

0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question