SQL help please??? 2

[qajussi]

Hi!
I am trying to write a sql statemet and having a little hard time wiht it.

Here is an example.

TblCompany
----------
CompanyID 11 12 13 14
CompanyName A B C D

TblBranch
----------
CompanyID 12 13 13 11 11
BranchID 11 12 14 12 14

a Company can be branches and vice versa.
There is a combo box of company name.
If I select one company name from it.
I want all the branch company name to show up in below textbox.
Say I select company A, i want its branch names to show up like B,D.
A-> B, D
B-> A
C-> B, D.

Can you help me with the SQL statement??
Thank you so much in adavance.

 

[r937]

select branch.CompanyName as BranchName
  from TblCompany as company
inner
  join TblBranch
    on company.CompanyID
     = TblBranch.CompanyID
inner
  join TblCompany as branch    
    on TblBranch.BranchID
     = branch.BranchID
 where company.CompanyName = 'A'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[qajussi]

Hi r937!
Thanks for your reply.
I tried your code but I am getting errors.
Can you see if this is working ??
Thanks millions.

 

[r937]

oops, two typos

select branch.CompanyName as BranchName
  from ( TblCompany as company
inner
  join TblBranch
    on company.CompanyID
     = TblBranch.CompanyID )
inner
  join TblCompany as branch    
    on TblBranch.BranchID
     = branch.CompanyID
 where company.CompanyName = 'A'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[qajussi]

Thank you very much.
That works fine now.

Here is the same code with the different fieldnames and i am asked to enter the parameter.
Can't seem to find the error..can you help??
I thought I changed them correctly.

SELECT branch.FacilityName AS BranchName
FROM (TblFacility AS company
INNER JOIN TblFacilityAffilBuilder
ON company.FacilityID = TblFacilityAffilBuilder.FacilityID)
INNER JOIN TblFacility AS branch
ON TblFacilityAffilBuilder.AffilFacilityID = branch.FacilityID
WHERE company.FacilityName="Onderstepoort Biological Products, Ltd.OBP";

 

[qajussi]

All I did was changed the
TblCompany 2 TblFacility
TblBranch 2 TblFacilityAffilBuilder

and companyID 2 FacilityID
companyName 2 FacilityName
branchID 2 AffilFacilityID

 

[qajussi]

I have been looking at this all day long..
Thank you so much for your help..
I need to get on with the SQL studying..

 

[PHV]

and i am asked to enter the parameter
Double check the spelling of the tables and fields.
Can you post the SQL code and the exact text of the input box ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[qajussi]

Thank you so much PHV.
r937's code works but when I changed the fieldnames I am getting the input box to enter the parameter.

here is the code.
SELECT branch.FacilityName AS BranchName
FROM (TblFacility AS company
INNER JOIN TblFacilityAffilBuilder
ON company.FacilityID = TblFacilityAffilBuilder.FacilityID)
INNER JOIN TblFacility AS branch
ON TblFacilityAffilBuilder.AffilFacilityID = branch.FacilityID
WHERE company.FacilityName="Onderstepoort Biological Products, Ltd.OBP";

TblFacility
FacilityID
FacilityName

TblFacilityAffilBuilder
FacilityID
AffilFacilityID <=== this is also facility ID.

Input box contains
TblFacilityAffilBuilder.AffilFacilityID
and asks to enter the parameter for that.

Another question:
I don't quite understand those join statement.
Can you break it down and explain for me if you can??
It's been a while I had done any sort of SQL writing.
Thanks millions as always, PHV.

 

[PHV]

You may try this:
SELECT branch.FacilityName AS BranchName
FROM TblFacility AS company INNER JOIN
(TblFacilityAffilBuilder INNER JOIN TblFacility AS branch
ON TblFacilityAffilBuilder.AffilFacilityID = branch.FacilityID)
ON company.FacilityID = TblFacilityAffilBuilder.FacilityID
WHERE company.FacilityName="Onderstepoort Biological Products, Ltd.OBP";

Or the initial query without WHERE clause.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244