A many-to-many relational problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A many-to-many relational problem

Author

Topic

sjlsam
Starting Member

9 Posts

Posted - 2007-06-15 : 15:22:32

My question:
How to have an unknown number of authors returned for a list of books, with the multiple authors being returned on the SAME row as each book

My table structure:


  Table A - Books
-------------------
BookID   | BookName
-------------------
1        | Book One

     Table B -
  RelatingTable
-------------------
BookID   | AuthorID
-------------------
1	 | 60
1	 | 61

 Table C - Authors
-------------------
AuthorID  | AuthorName
-------------------
60        | Bob
61        | Joe

My query:


SELECT * FROM Books, RelatingTable, Authors AS a
INNER JOIN Books 	AS b ON b.BookID   = r.BookID
INNER JOIN RelatedTable AS r ON r.AuthorID = a.AuthorID

Output I am getting:


-------------------------------------------------------------------------
b.BookID | b.BookName | r.BookID | r.AuthorID | a.AuthorID | a.AuthorName
---------|------------|----------|------------|------------|-------------
1	 | Book One   | 1        | 60	      | 60         | Bob
1	 | Book One   | 1        | 61	      | 61         | Joe
-------------------------------------------------------------------------

Desired output:


-----------------------------------------------------------------------------------------------------------------------------------------------------
b.BookID | b.BookName | r.BookID[0] | r.AuthorID[0] | a.AuthorID[0] | a.AuthorName[0] | r.BookID[1] | r.AuthorID[1] | a.AuthorID[1] | a.AuthorName[1]
---------|------------|-------------|---------------|---------------|-----------------|-------------|---------------|---------------|----------------
1        | Book One   | 1           | 60            | 60            | Bob             | 1           | 61            | 61            | Joe
-----------------------------------------------------------------------------------------------------------------------------------------------------

The desired output does not have to be like an array - I only assume thats what it would need to be

If you can post anything to help me get in the right direction I would really appreciate it
Thanks for your time!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 15:36:34

You are confusing presentation with data. You already have the SQL statement you need that gives you the exact data you want; the question is, how are you presenting this data and where? You should not try to create multiple columns in a database result to make things look a certain way, such as horizontally aligned in columns -- that is something for your presentation layer to handle. SQL is simply not designed to efficiently handle things like this; plus, what happens when an author has 10 books? Or 50 books? How many columns should it go to? There's no such thing as "column arrays" in SQL; sets of data is always represented in rows, not columns.

Also, if you have a result set with varying column names from call to call depending on how many authors and books there are, you can't do any SQL processing of the results since SQL is a declarative language where table and column names are constants; they should be known ahead of time and remain consistent until your database design changes.

Always focus on outputting and presenting data at your presentation layer, never at the database layer.

If you need help outputting these results in a certain way, that is question of how to do in ASP.NET or in Crystal or Excel or wherever it is you are ultimately displaying this data.

I hope this helps.

- Jeff
http://weblogs.sqlteam.com/JeffS

sjlsam
Starting Member

9 Posts

Posted - 2007-06-15 : 15:41:55

thanks jeff, that makes a lot of sense!

basically i am returning my results in coldfusion in a table format, the problem is i get the same book returned for however many authors are connected to it, when i want the book to be returned once, and then have a column that lists the authors for it

i have a UDF that converts my relationship into a comma list, but all this does for me is return two results for one book with two comma lists

it's like i need the relationship to exist, i just only want the comma list returned and not the JOINed columns
does that make sense?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 15:44:40

It's a cold fusion presentation issue you are having, not a SQL Server issue. I strongly advise you to do things the easiest, simplest, most direct and more efficient way and do your formatting in Cold Fusion. If you know SQL well but not cold fusion, then you should visit a cold fusion forum and ask for help there.

Also: why do you want BookID to be repeated over and over, as well as AuthorID being repeated at least twice per author? That makes no sense in either SQL or when you are presenting your output.

- Jeff
http://weblogs.sqlteam.com/JeffS

sjlsam
Starting Member

9 Posts

Posted - 2007-06-15 : 16:08:33

i was only repeating stuff to illustrate how i thought the columns might have to work if the many-many relationship returned on one row

the reason i was trying to get it to return on one row is because whenever people view the list of books they get something like
Book One by Bob
Book One by Joe

instead of something like Book One by Bob Joe
which i could reformat to be more presentable etc

I have tried using a comma UDF that COALESCE's my many-many into a list, but i still have a problem with the CFM output because the JOINs still exist
I get something like
Book one by Bob, Joe
Book one by Bob, Joe

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 16:11:31

Since you are not showing use your UDF code, it is hard to guess what you are doing, but you should not be joining at all if your UDF is what is returning a list of authors based on the bookID. You just need to:

select b.*, dbo.GetAuthors(bookID) as Authors
from books

... that's it.

- Jeff
http://weblogs.sqlteam.com/JeffS

sjlsam
Starting Member

9 Posts

Posted - 2007-06-15 : 16:15:44

yes but heres where things get more complex
even though i have a comma list - i still need the JOINs because you select to see the list of books by clicking on an authors name
for example

Click on Bob (&author=60)
Book One by Bob, Joe
Book Three by Bob

Click on Joe (&author=61)
Book One by Bob, Joe
Book Two by Joe

the relationship still needs to exist for filtering purposes!

the only other way i can think of getting this to work is doing something like

'Joe' LIKE authorlist which does not seem efficient to me

(so simply put: the comma list is used for display purposes only, the relationship is used to determine when certain books are displayed)

the other problem is that i need an accurate rowcount because i am using a next-n records script that is based on how many rows are returned

even if i am able to get CFM to not display the dupes, they will still be counted in the rowcount and thus mess up next-n records

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 20:01:03

You've confirm even more than before that your issue is 100% a DISPLAY and presentation issue in cold fusion. You have our SQL query done; pat yourself on the back and be happy that your database tier is all set for this task. You don't need a UDF, don't need to concatenate things into a CSV string, just return a simple resultset with a simple join, you already showed the sql for that in your original post.

Now open up cold fusion, find a good cold fusion reference book if you need it, visit a cold fusion forum, and get to work on writing the *presentation code* to display the data returned on your web page exactly how you need it. All you need to do is loop through your data and output the HTML that you need to align the authors horizontally. I know you can do this very, very easily in cold fusion, but if you try to force SQL to do it, with UDFs and other things that are completely unnecessary, it will only complicate both your SQL code AND your cold fusion code.

I can only repeat the same thing so many times, hopefully you will consider following my advice and give it a shot. You will be a better and more efficient programmer if you learn to do things in the proper place in your code.

Good luck.

- Jeff
http://weblogs.sqlteam.com/JeffS

sjlsam
Starting Member

9 Posts

Posted - 2007-06-15 : 20:10:16

thanks jeff you have really been a help

my issue with coldfusion is that i cannot get the code to display it in the format i want - a cfloop does not have a group="" field which is necessary to fixing the dupe returns - this is why i looked to solving the issue before cf even gets the results, thats why i turned to SQL UDF's

i will keep trying though, i will continue posting around on CFM boards and keep searching

at least now i understand that my SQL structures have been correct

thanks for your time

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 21:09:21

if cold fusion, just delcare a variable, call it "BookID" or something, and set it to the bookID of the first row. Then, as long as each row as the same bookID, you keep putting them all together into 1 row. If the bookID of the row is a different one, start a new row. It's the standard way to do grouping in a language like ASP or coldfusion where you loop through the rows in a query result and output them to the page.

so, in pseudo-code, you have something like this:

1) declare bookID variable, set it to -1

2) open up recordset, return rows

3) for each row in result:

a) if the row's BookID != the bookID variable: start a new row, output the book name, set bookID variable equal to bookID

b) output author info

4) loop

5) you are done!

- Jeff
http://weblogs.sqlteam.com/JeffS

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-15 : 21:12:40

I found something that might be helpful here:

http://docs.codecharge.com/studio3/html/ProgrammingTechniques/HowTo/SimpleReporting/SimpleReportWithGroupHeaders.html

UPDATE: an even better one might be here: http://www.brucephillips.name/blog/index.cfm/2006/12/26/Grouping-ColdFusion-Query-Results-On-Multiple-Columns

I think that shows you *exactly* what you need to do.

I just googled for "coldfusion query result grouping" and terms like that ...

- Jeff
http://weblogs.sqlteam.com/JeffS

sjlsam
Starting Member

9 Posts

Posted - 2007-06-15 : 21:23:54

jeff you've been most helpful
i think with those resources i will be able to solve my problem,
i can't tell you how long i've been scratching my head over this

the support on this forum appears to be of excellent quality!

thanks for all your help, i will try these techniques first thing when im back at work monday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources