Retrieve first row only in many-to-many relationship

[Gareth]

I have a db with three tables - books, sections, and a joining table.
The normal way of getting a many to many relationship (i.e. one book
may belong to many sections, and one section may contain many books)

I want to extract the data with a single row for each book so that I
only retrieve the first section description for any book. (e.g. title,
author, section, description)

Structure as follows:

tbl_book
book_id, title, author, description etc...

tbl_section
section_id, section_desc

tbl_book_section
book_id, section_id

DBA is away and I can't figure this out at all...any help gratefully
received.

[David Portas]

Try this. I'm assuming that by "first section" you mean the lowest numbered
section_id.

SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id

--
David Portas
------------
Please reply only to the newsgroup
--

[--CELKO--]

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Since SQL is a set-oriented language, there is no such concept as a
first row in a table. The next basic principle is that all
relationships are shown as values in a column. Therefore, you must
have a section number of some kind in the DDL that you did not post
for this to make sense.

Book_id ought to be an ISBN, but we have no idea what section_id is
like and if it has an ordering.

When the DBA gets back, ask him to read and use ISO-11179 naming
standards. What he ias given you says that you only have one book
about furniture, specifically tables.

[Gareth]

David

That did the trick thanks.

Gareth

"David Portas" <RE****************************@acm.org> wrote in message news:<ce********************@giganews.com>...

Try this. I'm assuming that by "first section" you mean the lowest numbered
section_id.

SELECT B1.book_id, B1.title, B1.author, B1.description,
S2.section_id, S2.section_desc
FROM tbl_book AS B1
JOIN
(SELECT book_id, MIN(section_id) AS section_id
FROM tbl_book_section
GROUP BY book_id) AS S1
ON B1.book_id = S1.book_id
JOIN tbl_section AS S2
ON S1.section_id = S2.section_id

[Gareth]

Joe

I don't know what you mean by DDL, but the other guy who posted a
reply clearly understood what I was asking about.

The database in question existed before the DBA (female by the way)
joined the company and the reason I want the query is to extract the
data for a new ecommerce system.

Naming conventions are indeed a good thing...
jo*******@northface.edu (--CELKO--) wrote in message news:<a2**************************@posting.google. com>...

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications.

Since SQL is a set-oriented language, there is no such concept as a
first row in a table. The next basic principle is that all
relationships are shown as values in a column. Therefore, you must
have a section number of some kind in the DDL that you did not post
for this to make sense.

Book_id ought to be an ISBN, but we have no idea what section_id is
like and if it has an ordering.

When the DBA gets back, ask him to read and use ISO-11179 naming
standards. What he ias given you says that you only have one book
about furniture, specifically tables.

[David Portas]

> I don't know what you mean by DDL, but the other guy who posted a

reply clearly understood what I was asking about.

I guessed what you wanted but it is useful to post DDL for questions like
this:
www.aspfaq.com/5006

--
David Portas
------------
Please reply only to the newsgroup
--

[Greg D. Moore \(Strider\)]

"Gareth" <ga*******@hotmail.com> wrote in message
news:c1**************************@posting.google.c om...

Joe

I don't know what you mean by DDL, but the other guy who posted a
reply clearly understood what I was asking about.
DDL - Data Description Language.

Basically the SQL commands to create the tables with keys, constraints, etc.
that you want. This allows folks answering your question to create a test
setup on their own servers. Generally you'll get answers that have been
fully tested that way.

Joe Celko is a bit of curmudgeon, but he's also arguably one of the better
experts on the SQL language out there. He has several books to his name and
knows his stuff. And yes, he's opinionated. :-)

The database in question existed before the DBA (female by the way)
joined the company and the reason I want the query is to extract the
data for a new ecommerce system.

Naming conventions are indeed a good thing...

[Gareth]

Greg - DDL - makes sense now...

In future I'll do this - didn't realise the conventions in the group.

Thanks

Gareth

[Joe Celko]

>> I don't know what you mean by DDL .. <<

Data Definition Language. SQL has three sublanguages and this is one of
them. It is also the minimal netiquette in SQL newsgroups.

the other guy who posted a reply clearly understood what I was asking

about. <<

No, he guessed lucky. What if section_id had been a title, like
"Introduction" or "preamble" which was not in alphabetic order?

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[--CELKO--]

>> Joe Celko is a bit of curmudgeon, ... <<

Hey, if I had any friends, they'd tell you what a great guy I am!

[Greg D. Moore \(Strider\)]

"--CELKO--" <jo*******@northface.edu> wrote in message
news:a2**************************@posting.google.c om...

Joe Celko is a bit of curmudgeon, ... <<

Hey, if I had any friends, they'd tell you what a great guy I am!

Hey, you say that like I was saying something that wasn't nice. :-)