Query - One-to-Many question

Tina

>Basically, if the subject is "ME", then I want to select that row.
>If there is no row for that catalog_number that has a subject of "ME",
>then I want to grab the row that has a course_offer_number of '1'
>and a subject that is not equal to "ME".

Is this what you mean?

SELECT
FRM course_table c
JIN course_subject s N c.course_id=s.course_id
WHERE s.subject="ME" R (s.course_offer_number=1 AND s.subject <"ME");

PB

Tina Matter wrote:
I have two tables:
>
1.) A course table (stores course_id and catalog_number)
2.) A course_subject table (stores course_id, catalog_number,
subject, and course_offer_number)
>
For each row in the course_table, there can be many rows in the
course_subject table, due to cross-postings among different departments.
>
Here is what I'd like to do.
>
I would like to grab a list of catalog_numbers from the course_table,
but I only want one row in the course_subject table.
Basically, if the subject is "ME", then I want to select that row.
If there is no row for that catalog_number that has a subject of "ME",
then I want to grab the row that has a course_offer_number of '1' and
a subject that is not equal to "ME".
I am basically only grabbing the subject field from the course_subject
table. If an "ME" subject exists for a catalog_number, grab that
one. , grab whatever other one exists. This is assuming
that there will only be one other one.
>
Does this make sense? I'd really like to know if there's a way to do
this in one query. I can probably do it in PHP with multiple
selects, building my list as I go. But if there's a fancy way to do
this in one query, then I'd much rather do it that way.
>
Thanks for any help.
Tina
>
>

>
>
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM
>

Tina,

>for some reason, it still pulled all of the rows

Are there multiple rows which meet your WHERE condition? If so, and if
you want just one of them, your need another WHERE condition.

PB



Tina Matter wrote:
Peter,
>
That was the first query I tried, but for some reason, it still pulled
all of the rows. So I've been trying to come up with another solution.
>
Any other ideas?
>
Thanks for the reply.
Tina
>
>
>
Peter Brawley wrote, 6/26/08 2:12 PM:
>Tina
>>
>>Basically, if the subject is "ME", then I want to select that row.
>>If there is no row for that catalog_number that has a subject of "ME",
>>then I want to grab the row that has a course_offer_number of '1'
>>and a subject that is not equal to "ME".
>>
>Is this what you mean?
>>
>SELECT
>FRM course_table c
>JIN course_subject s N c.course_id=s.course_id
>WHERE s.subject="ME" R (s.course_offer_number=1 AND s.subject <"ME");
>>
>PB
>>
>Tina Matter wrote:
I have two tables:

1.) A course table (stores course_id and catalog_number)
2.) A course_subject table (stores course_id, catalog_number,
subject, and course_offer_number)

For each row in the course_table, there can be many rows in the
course_subject table, due to cross-postings among different
departments.

Here is what I'd like to do.

I would like to grab a list of catalog_numbers from the
course_table, but I only want one row in the course_subject table.
Basically, if the subject is "ME", then I want to select that row.
If there is no row for that catalog_number that has a subject of
"ME", then I want to grab the row that has a course_offer_number of
'1' and a subject that is not equal to "ME".
I am basically only grabbing the subject field from the
course_subject table. If an "ME" subject exists for a
catalog_number, grab that one. , grab whatever other one
exists. This is assuming that there will only be one other one.

Does this make sense? I'd really like to know if there's a way to
do this in one query. I can probably do it in PHP with multiple
selects, building my list as I go. But if there's a fancy way to
do this in one query, then I'd much rather do it that way.

Thanks for any help.
Tina






No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 -
Release Date: 6/26/2008 11:20 AM

>>
>

>
>
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM
>

Tina,

>Even if I do this simple query, while hardcoding in a catalog_number:

>SELECT subject, catalog_number FRM course_subject
>WHERE (catalog_number = 520) AND
>((subject = 'ME') R ((course_offer_number = 1) AND (subject NT LIKE
'ME')))

Errrm, you mean subject <'ME', don't you!?

>I still get two rows back
>So I'm not sure what else I need in my Where Clause

You said the query spec was rows with a given catalog_number AND
(subject='ME' R (course_offer_number of '1' and subject <'ME')).
There are two such rows for catalog number 520. What do you want your
query to return in that instance?

PB



Tina Matter wrote:
Even if I do a basic select (with no joins) for a given
catalog_number, I still get two rows back.
>
Even if I do this simple query, while hardcoding in a catalog_number:
>
SELECT subject, catalog_number FRM course_subject
WHERE (catalog_number = 520) AND
((subject = 'ME') R ((course_offer_number = 1) AND (subject NT LIKE
'ME')))
>
>
I still get two rows back:
>
subject catalog_number
ME 520
MSE 520
>
>
So I'm not sure what else I need in my Where Clause
>
Thanks bunches.
Tina
>
>
Peter Brawley wrote, 6/26/08 3:33 PM:
>Tina,
>>
>>for some reason, it still pulled all of the rows
>>
>Are there multiple rows which meet your WHERE condition? If so, and
>if you want just one of them, your need another WHERE condition.
>>
>PB
>>
>
>>
>Tina Matter wrote:
Peter,

That was the first query I tried, but for some reason, it still
pulled all of the rows. So I've been trying to come up with another
solution.

Any other ideas?

Thanks for the reply.
Tina



Peter Brawley wrote, 6/26/08 2:12 PM:
Tina

>Basically, if the subject is "ME", then I want to select that
row. >If there is no row for that catalog_number that has a
subject of "ME",
>then I want to grab the row that has a course_offer_number of '1'
>and a subject that is not equal to "ME".

Is this what you mean?

SELECT
FRM course_table c
JIN course_subject s N c.course_id=s.course_id
WHERE s.subject="ME" R (s.course_offer_number=1 AND s.subject <
"ME");

PB

Tina Matter wrote:
I have two tables:

1.) A course table (stores course_id and catalog_number)
2.) A course_subject table (stores course_id, catalog_number,
subject, and course_offer_number)

For each row in the course_table, there can be many rows in the
course_subject table, due to cross-postings among different
departments.

Here is what I'd like to do.

I would like to grab a list of catalog_numbers from the
course_table, but I only want one row in the course_subject table.
Basically, if the subject is "ME", then I want to select that
row. If there is no row for that catalog_number that has a
subject of "ME", then I want to grab the row that has a
course_offer_number of '1' and a subject that is not equal to "ME".
I am basically only grabbing the subject field from the
course_subject table. If an "ME" subject exists for a
catalog_number, grab that one. , grab whatever other
one exists. This is assuming that there will only be one other
one.

Does this make sense? I'd really like to know if there's a way
to do this in one query. I can probably do it in PHP with
multiple selects, building my list as I go. But if there's a
fancy way to do this in one query, then I'd much rather do it that
way.

Thanks for any help.
Tina






No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 -
Release Date: 6/26/2008 11:20 AM







No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 -
Release Date: 6/26/2008 11:20 AM

>>
>

>
>
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.101 / Virus Database: 270.4.1/1521 - Release Date: 6/26/2008 11:20 AM
>

Tina

>What I want is NLY the 'ME' row (if a row exists with a subject of
'ME').
>If an 'ME' subject row does not exist, then I want the other one.

I see. Then to complete spec, what behaviour is desired when there are
two rows with 'ME', or two rows with (course_offer_number = 1 AND
subject NT LIKE 'ME')?

>I get the same results with these two statements:
>(subject NT LIKE 'ME')
>(subject <'ME')
>So I didn't really see a difference.

Indeed. The difference is efficiency. To see the effect, try it on a
large table.

PB



Tina Matter wrote:
Peter,
>
Yes, I know there are two rows in the course_subject table with a
catalog_number of 520. has a subject of 'ME' and the other has a
subject of 'MSE'.
What I want is NLY the 'ME' row (if a row exists with a subject of
'ME'). If an 'ME' subject row does not exist, then I want the other
one.
This is just one example. There are other catalog_numbers that will
have two rows. There may also be catalog_numbers that only show one
row (that does not have a subject of 'ME').
>
FYI:
>
I get the same results with these two statements:
>
(subject NT LIKE 'ME')
(subject <'ME')
>
So I didn't really see a difference.
>
Thank you.
Tina
>
>
>
Peter Brawley wrote, 6/26/08 6:11 PM:
>Tina,
>>
>>Even if I do this simple query, while hardcoding in a catalog_number:
>>
>>SELECT subject, catalog_number FRM course_subject
>>WHERE (catalog_number = 520) AND
>>((subject = 'ME') R ((course_offer_number = 1) AND (subject NT
>LIKE 'ME')))
>>
>Errrm, you mean subject <'ME', don't you!?
>>
>>I still get two rows back
>>So I'm not sure what else I need in my Where Clause
>>
>You said the query spec was rows with a given catalog_number AND
>(subject='ME' R (course_offer_number of '1' and subject <'ME')).
>There are two such rows for catalog number 520. What do you want your
>query to return in that instance?
>>
>PB
>>
>
>>
>Tina Matter wrote:
Even if I do a basic select (with no joins) for a given
catalog_number, I still get two rows back.

Even if I do this simple query, while hardcoding in a catalog_number:

SELECT subject, catalog_number FRM course_subject
WHERE (catalog_number = 520) AND
((subject = 'ME') R ((course_offer_number = 1) AND (subject NT
LIKE 'ME')))


I still get two rows back:

subject catalog_number
ME 520
MSE 520


So I'm not sure what else I need in my Where Clause

Thanks bunches.
Tina


Peter Brawley wrote, 6/26/08 3:33 PM:
Tina,

>for some reason, it still pulled all of the rows

Are there multiple rows which meet your WHERE condition? If so, and
if you want just one of them, your need another WHERE condition.

PB



Tina Matter wrote:
Peter,

That was the first query I tried, but for some reason, it still
pulled all of the rows. So I've been trying to come up with
another solution.

Any other ideas?

Thanks for the reply.
Tina



Peter Brawley wrote, 6/26/08 2:12 PM:
Tina

>Basically, if the subject is "ME", then I want to select that
row. >If there is no row for that catalog_number that has a
subject of "ME",
>then I want to grab the row that has a course_offer_number of '1'
>and a subject that is not equal to "ME".

Is this what you mean?

SELECT
FRM course_table c
JIN course_subject s N c.course_id=s.course_id
WHERE s.subject="ME" R (s.course_offer_number=1 AND s.subject <
"ME");

PB

Tina Matter wrote:
I have two tables:

1.) A course table (stores course_id and catalog_number)
2.) A course_subject table (stores course_id, catalog_number,
subject, and course_offer_number)

For each row in the course_table, there can be many rows in the
course_subject table, due to cross-postings among different
departments.

Here is what I'd like to do.

I would like to grab a list of catalog_numbers from the
course_table, but I only want one row in the course_subject table.
Basically, if the subject is "ME", then I want to select that
row. If there is no row for that catalog_number that has a
subject of "ME", then I want to grab the row that has a
course_offer_number of '1' and a subject that is not equal to "ME".
I am basically only grabbing the subject field from the
course_subject table. If an "ME" subject exists for a
catalog_number, grab that one. , grab whatever other
one exists. This is assuming that there will only be one
other one.

Does this make sense? I'd really like to know if there's a way
to do this in one query. I can probably do it in PHP with
multiple selects, building my list as I go. But if there's a
fancy way to do this in one query, then I'd much rather do it
that way.

Thanks for any help.
Tina






No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521
- Release Date: 6/26/2008 11:20 AM







No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 -
Release Date: 6/26/2008 11:20 AM







No virus found in this incoming message.
Checked by AVG. Version: 8.0.101 / Virus Database: 270.4.1/1521 -
Release Date: 6/26/2008 11:20 AM

>>
>

>
>
No virus found in this incoming message.
Checked by AVG.
Version: 8.0.101 / Virus Database: 270.4.1/1522 - Release Date: 6/27/2008 8:27 AM
>