Help with SQL query

Please bare with me in trying to get my point across, I'm new to SQL and
would appreciate the help so much.
I have two table Table1 and Table2. There is a one to many relationship
between Table1 and Table2. Table2 has a list of widgets, I need to create a
query that would show the records from Table1 along with it's related
records and all non related records from Table2. So if I have 5 widgets and
only two have related records in Table1 results should be
Table1ID(2) fkTable2 Table2(widgetname1)
Table1ID(2) fkTable2 Table2(widgetname2)
null null Table2(widgetname3)
null null Table2(widgetname4)
null null Table2(widgetname5)
When I filter the query on another Table1ID, I would need the same sort of
result for each Table1ID.try this
SELECT TABLE1.COL1,TABLE1.COL2,TABLE2.COL1,TABLE2.COL2 FROM TABLE1 RIGHT
OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL1
--
Regards
R.D
--Knowledge gets doubled when shared
"Tim Harvey" wrote:

> Please bare with me in trying to get my point across, I'm new to SQL and
> would appreciate the help so much.
> I have two table Table1 and Table2. There is a one to many relationship
> between Table1 and Table2. Table2 has a list of widgets, I need to create
a
> query that would show the records from Table1 along with it's related
> records and all non related records from Table2. So if I have 5 widgets an
d
> only two have related records in Table1 results should be
> Table1ID(2) fkTable2 Table2(widgetname1)
> Table1ID(2) fkTable2 Table2(widgetname2)
> null null Table2(widgetname3)
> null null Table2(widgetname4)
> null null Table2(widgetname5)
> When I filter the query on another Table1ID, I would need the same sort of
> result for each Table1ID.
>
>
>

Help with Query

I have two columns I want to compare, both varchar, and
would be in this format
Subject | Instructor
ACCT101-nnnnn | jcdoe
ACCT101-nnnnn | jcdoe
ACCT102-nnnnn | jcdoe
ACCT102-nnnnn | jcsmith
The subject is made up of (Table.Subject + '-' +
CourseNumber)
What I want to do, is query for those cases where the
Table.Subject(ie ACCT101)has the same instructor for each
instance. So if one teacher was teaching the same
section of the course my query would be like this, from
the above example:
Subject | Instructor
ACCT101 | jcdoe
Does anyone know how I could do this?
Thanks.
You could try this:
select
substring(Subject, charindex('-', Subject)) as Subject,
min(Instructor) as Instructor
from yourTable
group by substring(Subject, charindex('-', Subject))
having min(Instructor) = max(Instructor
or
select distinct
substring(Subject, charindex('-', Subject)) as Subject,
Instructor
from yourTable
where not exists (
select * from yourTable Tcopy
where substring(Tcopy.Subject, charindex('-', Tcopy.Subject)) =
substring(yourTable.Subject, charindex('-', yourTable.Subject))
and Tcopy.Instructor <> yourTable.Instructor
)
Just a suggestion. If the two pieces of the [Subject] column have
independent meanings in your table, you might consider keeping them in
separate columns to avoid having to use SUBSTRING to get the information
out.
Steve Kass
Drew University
spacejunk wrote:

>I have two columns I want to compare, both varchar, and
>would be in this format
>Subject | Instructor
>--
>ACCT101-nnnnn | jcdoe
>ACCT101-nnnnn | jcdoe
>ACCT102-nnnnn | jcdoe
>ACCT102-nnnnn | jcsmith
>--
>The subject is made up of (Table.Subject + '-' +
>CourseNumber)
>What I want to do, is query for those cases where the
>Table.Subject(ie ACCT101)has the same instructor for each
>instance. So if one teacher was teaching the same
>section of the course my query would be like this, from
>the above example:
>Subject | Instructor
>--
>ACCT101 | jcdoe
>--
>Does anyone know how I could do this?
>Thanks.
>
>