array & multilist

Question

array & multilist

harshinderkaur

SSC Rookie

Points: 32
More actions
August 22, 2010 at 1:10 am

#95006

the syntax for using array & multilist as given in d dbms book korth does NOT 🙁 work in mysql or in msql 2000. anyone has any idea how do i use array & multilist in sql itself.......

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

harshinderkaur (8/22/2010)
the syntax for using array & multilist as given in d dbms book korth does NOT 🙁 work in mysql or in msql 2000. anyone has any idea how do i use array & multilist in sql itself.......

I have no idea what a "multilist" is but I do have an idea of what arrays are. They don't exist in SQL Server BUT, you can fake it. I just need to know a bit more about what you're trying to do. Be gentle because I'm neither a mathematical array or matrix wiz.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

harshinderkaur SSC Rookie Points: 32 More actions · Answer 2

thanx for replying jeff. i want to create a table named book & an array to store the name of authors(maximum 10) of any book . each author name is of the type varchar. kindly reply

WayneS SSC Guru Points: 95461 More actions · Answer 3

How about:

CREATE TABLE dbo.Book (

Book_ID int PRIMARY KEY CLUSTERED,

ISDN varchar(20?),

Title varchar(100),

etc.)

CREATE TABLE dbo.Author(

Author_ID int PRIMARY KEY CLUSTERED,

First_Name varchar(20),

Last_Name varchar(20),

etc.)

CREATE TABLE dbo.Book_Author(

Book_ID int,

Author_ID int,

PRIMARY KEY (book_id, author_id))

with Foreign keys to the book and author tables.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 4

harshinderkaur (8/24/2010)
thanx for replying jeff. i want to create a table named book & an array to store the name of authors(maximum 10) of any book . each author name is of the type varchar. kindly reply

Wayne pretty much sussed it with his table example above. The "middle" table is called a "join table", "cross reference table", or "associative table" (to name a few) and will allow a single book to be related to as many authors as you want without any limits (I know of at least one computer "deep dive" book with dozens of authors).

Will that do it for you or is there something we're missing?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)