Help with SELECT  
Author Message
MMullett\(BTInternet\)





PostPosted: Wed Jan 11 10:17:20 CST 2006 Top

SQL Server Developer >> Help with SELECT

Hi all,

I have the following tables: Member, Dependent, Doctor.

I am trying to get, based on the name entered, all members and dependents
that have seen a certain doctor.

For example, I supply the last name SMITH and the docotor ID 1234567890. I
need to get all of the members with the last name of SMITH and all of the
dependents with the last name of SMITH who have records in the docotor table
for the doctor with the ID 1234567890.

I can do this seperatley for the Member and Dependent, but I need to wind up
with one set of results with both the members and dependents sorted by name.

I am writing this for a web site in vbscript, so I did not think that
creating tables would be the best solutions.

Any ideas on how to wind up with the data needed?

Thanks in advance,

George

SQL Server291  
 
 
Jens





PostPosted: Wed Jan 11 10:17:20 CST 2006 Top

SQL Server Developer >> Help with SELECT Woudl be good to provide some more sample data / DDL.

http://www.aspfaq.com/5006


HTH, Jens Suessmeyer.

 
 
Jim





PostPosted: Wed Jan 11 10:44:51 CST 2006 Top

SQL Server Developer >> Help with SELECT If I understand your issue correctly, you just need to use a union.

Select memberName as name, DoctorID
from tblmember
inner join tbldoctor on tblmember.doctorid = tbldoctor.doctorid
union all
Select DependentName as name, DoctorID
from tblDependent
inner join tbldoctor on tblDependent.doctorid = tbldoctor.doctorid

You could also create a view that contains all records from both the
dependent and the member table, then join to that. This is ideal if you
will be doing this sort of thing often (which you probably will).

Select memberName as name, DoctorID [any other applicable fields] from
tblmember
union all
Select DependentName as name, DoctorID [any other applicable fields] from
tblDependent

Either way you will probably want to make the query into a view which you
can then select from and pass the name in without caring about the
underlying SQL of the joins and unions.



> Hi all,
>
> I have the following tables: Member, Dependent, Doctor.
>
> I am trying to get, based on the name entered, all members and dependents
> that have seen a certain doctor.
>
> For example, I supply the last name SMITH and the docotor ID 1234567890.
I
> need to get all of the members with the last name of SMITH and all of the
> dependents with the last name of SMITH who have records in the docotor
table
> for the doctor with the ID 1234567890.
>
> I can do this seperatley for the Member and Dependent, but I need to wind
up
> with one set of results with both the members and dependents sorted by
name.
>
> I am writing this for a web site in vbscript, so I did not think that
> creating tables would be the best solutions.
>
> Any ideas on how to wind up with the data needed?
>
> Thanks in advance,
>
> George