473,378 Members | 1,469 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

Retrieve first row only in many-to-many relationship

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.
Jul 20 '05 #1
10 14471
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
--
Jul 20 '05 #2
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.
Jul 20 '05 #3
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

Jul 20 '05 #4
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.

Jul 20 '05 #5
> 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
--
Jul 20 '05 #6

"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...

Jul 20 '05 #7
Greg - DDL - makes sense now...

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

Thanks

Gareth
Jul 20 '05 #8
>> 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!
Jul 20 '05 #9
>> Joe Celko is a bit of curmudgeon, ... <<

Hey, if I had any friends, they'd tell you what a great guy I am!
Jul 20 '05 #10

"--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. :-)

Jul 20 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: jandt | last post by:
I know that you can retrieve data from a form using Request.Form("name"), but how do you retrieve a file?
2
by: js | last post by:
I have a table rendered with XSLT. The first column has a radio button controls for user to make a selection for a particular row. All the values in the remaining columns are all concated with a...
1
by: DaveF | last post by:
How can I retrieve display_names Dim custDS As DataSet = New DataSet Dim mySQL, mySQL2, mySQL3 As String
5
by: Hannie | last post by:
I am currently doing my project using VB.NET I have a database which contains all my data that the user key in. The problem is, when i click the retrieve button on the form, the details that the...
1
by: barbara_dave | last post by:
Hi, all, I want to use several text box showinh the data records of an Access database. I open a db connection, set a data adapter and a dataset to the table of that database. I set databinding...
3
by: monomaniac21 | last post by:
hi all im querying a db for two rows which are always returned. how can i reference each row and output its contents without using a while loop. ive tried: $row = mysql_fetch_array($result);...
1
by: Cem | last post by:
Hi, in GridView1 I have following code: <ItemTemplate> <asp:ImageButton ID="ImageButton1" runat="server" ImageUrl='<%# Eval("airport_active", "images/icons/ico_airport_active_{0}.gif") %>'...
4
by: somank.sharma | last post by:
I am running an exe created in C. I need the full path (absolute path) of this exe. In the first argument of main, I am getting the exe name. How can I get the full path for this exe.
2
by: singhanand2009 | last post by:
how could i retrieve data from database for a particular field. example- i have got a table having id as primary key, password, and other entity. how could i retrieve only one colum such as id.
1
by: sbettadpur | last post by:
Hi everybody, Iam struggling for retrieve mails from mail server using perl iam facing some problem in blow my code even i have the mails in my mailserver iam not getting the count of mails...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.