creative ideas on problem needed

Hello! I am looking for creative ideas to the following situation.
Currently I receive multiple .dbf files coming in from multiple locations at
the end of the day. I then use DTS to bring those files into their
appropriate tables in a sql database.
However, now we would like to get the information real time from these
multiple locations that are on our WAN. So there are multiple remote.db
databases sitting on different workstations on the WAN that we would like to
get data from and pull back to our central MS Sql 2000 database. It can pol
l
every 15 minutes, every time an update hits remote.db, or every
hour...Doesn't matter, we just don't want to wait until the end of the day.
We can easily install an application on the remote workstations that have
remote.db on.
Any high level ideas are appreciated! Using any technologies...
Thanks, AshleyTAshley,
Have you looked in to the Replication capabilities of SQL Server?
Barry|||Why don't you use replication?|||Can I get the data from a Sybase Adaptive SqlAnywhere databases acrosse the
WAN?
Thank you,
"Alexander Kuznetsov" wrote:

> Why don't you use replication?
>|||>> we would like to get the information real time from these
multiple locations that are on our WAN. <<
A 15 minute cycle is not real time. Have you looked at products that
are designed for streaming data? Stonebraker's Streambase, Kx Systems,
etc. are built to handle things like stock market feeds in real time.

creating unique index and clustered index in sql server management studio

Hello,
I have run across a very strange situation.
If i use the management studio to change an index that is clustered is does
not actually change the index. It shows up in the gui as changed, but the
data is not changed when returning the data.
Also when you use the createto> when right clicking on the table, none of
the indexes or index constraints are recreated.
In order to make the changes work you have to manually create the
constraints:
ie
ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
IX_key_messages_initiated_from UNIQUE CLUSTERED
(
key_messages_initiated_from
) ON [PRIMARY]
CREATE NONCLUSTERED INDEX IX_initiated_from ON dbo._messages_initiated_from
(
initiated_from
) ON [PRIMARY]
Then the changes work and the code is genereted in the scripts.
This used to work in enterprise manager in sql 2000.
Any ideas why this is not functioning right?
Thanks
gary
See in-line:
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I have run across a very strange situation.
> If i use the management studio to change an index that is clustered is
> does not actually change the index. It shows up in the gui as changed,
> but the data is not changed when returning the data.
The order in which data is returned is never guarenteed unless you use an
ORDER BY clause. This has always been the case and is nothing new. Without
an ORDER BY clause SQL Server is free to return the rows in what ever order
it seems most appropriate. Often times when you have a clustered index on a
table and issue a SELECT without a WHERE clause it will return the data in
mostly clustered index order. But this has never been guarenteed and should
not rely on that behaviour.

> Also when you use the createto> when right clicking on the table, none of
> the indexes or index constraints are recreated.
> In order to make the changes work you have to manually create the
> constraints:
If you want all the Indexes, constraints etc. you should use the Generate
Scripts wizard found by right clicking on the DB under Tasks. There you can
pick and choose all objects or none.

> ie
> ALTER TABLE dbo._messages_initiated_from ADD CONSTRAINT
> IX_key_messages_initiated_from UNIQUE CLUSTERED
> (
> key_messages_initiated_from
> ) ON [PRIMARY]
> CREATE NONCLUSTERED INDEX IX_initiated_from ON
> dbo._messages_initiated_from
> (
> initiated_from
> ) ON [PRIMARY]
> Then the changes work and the code is genereted in the scripts.
> This used to work in enterprise manager in sql 2000.
> Any ideas why this is not functioning right?
>
> Thanks
> gary
>
|||This response does not completely answer the question. First off, if the
index is clustered, the data is returned in the order of the records, which
is the order of the cluster. That is the definition.
Second of all, if you right click on the table, and select createto> to
create the script, absolutely none of the indexes that you created manually
other than the primary index appear in the script.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||I mean, when you right click on a table and select "script table to -->
create to--> the code to create the other indexes is not created.
if you were to create the indexes as constraints as the previous version did
in the enterprise manager then the script to works.
Also, on clustered indexes created with a constraint the unique setting is
automatically yes and can't be changed (it is disabled). In the new sql
console you can create a clustered index and then mark it as non-unique,
which just does not make sense as the clustered index is what all the other
indexes point to.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Ok, i guess i can assume the indexes are being created differently. But it
still does not make sense that when the tables are scripted that the script
to create the new indexes are not showing up.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||No, that's not the definition of a clustered index. The rows a STORED in
the order of the clustered index. They're returned in whatever order SQL
feels like if you don't specify an ORDER BY.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"gary" <garyseven7@.nospam.nospam> wrote in message
news:eyTC0jYDGHA.740@.TK2MSFTNGP12.phx.gbl...
> This response does not completely answer the question. First off, if the
> index is clustered, the data is returned in the order of the records,
> which is the order of the cluster. That is the definition.
> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
|||gary (garyseven7@.nospam.nospam) writes:
> This response does not completely answer the question. First off, if
> the index is clustered, the data is returned in the order of the
> records, which is the order of the cluster. That is the definition.
NO!
The fact that index is clustered means that the data is at the leaf
pages of the index. Thus, data is physically sorted according to the
clustered index.
But SQL does not bother about physical storage. If you say:
SELECT ... FROM tbl WHERE ...
without an ORDER BY clause, the DB engine is free to return the rows
in any order. For instance, if the WHERE clause includes a column on
which there is an non-clustered index, it is likely that that the
output will follow that index. If you say "SELECT * FROM bigtable",
SQL Server may open parallel streams, and run from different parts of
the table, at the same time.
In a relational datbase, there is only one way get data sorted in a
certain order, and that is ORDER BY.

> Second of all, if you right click on the table, and select createto> to
> create the script, absolutely none of the indexes that you created
> manually other than the primary index appear in the script.
Yes. Andrew addressed this in his post:
[vbcol=seagreen]

> Also, on clustered indexes created with a constraint the unique setting
> is automatically yes and can't be changed (it is disabled). In the new
> sql console you can create a clustered index and then mark it as
> non-unique, which just does not make sense as the clustered index is
> what all the other indexes point to.
What happens when you define a clustered index as non-unique, is that
SQL Server adds a four-byte "uniquifier". It has been this way since
SQL 7. (And all SQL Server versions have permitted non-uniuqe clustered
indexes.)

> Ok, i guess i can assume the indexes are being created differently. But
> it still does not make sense that when the tables are scripted that the
> script to create the new indexes are not showing up.
On http://lab.msdn.microsoft.com/produc...k/default.aspx you
can enter suggestions for improvments in SQL Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||Thanks for all your help.
It is kind of odd that the script table does not generate the indexes and
the script database that is in the wizard is capable of generating the
sciprts for the index (it doesn't generate them by default, you have to
select the script indexes to be true).
SQL 2000 GUI created the indexes by creating a constraint while the new SQL
2005 GUI creates them using the create index statement. If created as a
constraint the alter statement ins generated in the create table, if created
in a create index statement it is not.
This seems somewhat consistantly inconsistant so to speak.
Thanks for helping clarify the issue.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
> See in-line:
> --
> Andrew J. Kelly SQL MVP
>
> "gary" <garyseven7@.nospam.nospam> wrote in message
> news:%23NRW54XDGHA.3444@.TK2MSFTNGP10.phx.gbl...
>
> The order in which data is returned is never guarenteed unless you use an
> ORDER BY clause. This has always been the case and is nothing new.
> Without an ORDER BY clause SQL Server is free to return the rows in what
> ever order it seems most appropriate. Often times when you have a
> clustered index on a table and issue a SELECT without a WHERE clause it
> will return the data in mostly clustered index order. But this has never
> been guarenteed and should not rely on that behaviour.
>
>
> If you want all the Indexes, constraints etc. you should use the Generate
> Scripts wizard found by right clicking on the DB under Tasks. There you
> can pick and choose all objects or none.
>
>
|||Yes I agree that it is misleading and not a very good design that it leaves
out a bunch of objects. Its really only useful for a quick look at the DDL
of the table itself. Anything more should be done thru the Generate Scripts
task.
Andrew J. Kelly SQL MVP
"gary" <garyseven7@.nospam.nospam> wrote in message
news:OaEMUSaDGHA.4052@.TK2MSFTNGP10.phx.gbl...
> Thanks for all your help.
> It is kind of odd that the script table does not generate the indexes and
> the script database that is in the wizard is capable of generating the
> sciprts for the index (it doesn't generate them by default, you have to
> select the script indexes to be true).
> SQL 2000 GUI created the indexes by creating a constraint while the new
> SQL 2005 GUI creates them using the create index statement. If created
> as a constraint the alter statement ins generated in the create table, if
> created in a create index statement it is not.
> This seems somewhat consistantly inconsistant so to speak.
> Thanks for helping clarify the issue.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:e4Bl$HYDGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
sql

creating table fields dynamically from another table row

Hello all:

Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.

The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.

create dummy emp (
field1 VARCHAR(100), field2 varchar(200), field3 VARCHAR(100)... )

Table: Dummy
================================================== ==
field1 field2 field3........
Empid Empname empaage1 sam 23.........
2 rai 22.........
.
.
.
n raj 45..........
================================================== ==
Now I want to create another table as "EMP" , with proper data type
fields too..

create table emp (
empid int, empname varchar(100), empage int...)

The table should look like as:

Table: EMP
================================================== ==
Empid Empname empaage.........
1 sam 23............
2 rai 22............
.
.
.
n raj 45.............
================================================== ==

I want to do this dynamically....
Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]

A small rough snippet of the code will be appreciated...

Waiting for replies......
sabyyou can do something like below. but determining the exact filed type in CREATE table is difficult and avoiding that I have taken all fields as varchar(100)

create table #T (SQLTxt varchar(5000))

declare @.SQL varchar(5000)
declare @.FinalSQL varchar(5000)
declare @.ii int

set @.SQL=''
set @.ii=1
while @.ii <= 140
begin
set @.SQL = @.SQL + ' Field' + cast(@.ii as varchar) + ' + '' varchar(100) null,''+'
set @.ii=@.ii+1
end
set @.SQL = left(@.SQL,len(@.SQL)-3)+''''
set @.SQL = 'insert into #T (SQLTxt) select top 1 ' + '''create table Dummy2 (''+' + @.SQL + '+'')''' + ' from Dummy'

exec (@.SQL)
set @.FinalSQL = (select top 1 SQLTxt from #T)
exec (@.FinalSQL)
drop table #T

Creating Peer-to-Peer Merge Replication

We have a situation exactly like that shown in the documentation for creating a peer-to-peer merge replication. That is, we have three servers, one each in Chicago, New York, and Bermuda. Users in each office will read and write to their respective servers and the servers will then replicate with one another. The documentation, however, only shows how to set up peer-to-peer for a transactional replication. The problem is that the various Manger Studio options and the database stored procedures are NOT the same between transactional and merge replication and on the face of it, merged replication does not support a peer-to-peer topology. Can somebody walk me through the process of creating a peer-to-peer merge replication, or else convince me that I should go with transaction with updating subscriptions -- in contravention of what the documentation seems to reccommend? I'd really appreciate it!

Thanks.

Randy

before any recommendations, what are your business requirements in terms of latency? regarding a merge replication, is there something else you require that prohibits you from doing a standard publication with two subscriptions?|||

In terms of business requirements the only issue is that three distinct set of users, in three different parts of the world, are updaing their own local servers but that update information needs to be reflected on all servers. The way my boss and I read the documentation, the two options are merge or transaction with updatable subscriptions. Between the two options, the documentation seems to encourage peer-to-peer merge replication.

As for latency, we're probably talking less than 50 transactions per second. Indeed, I suspect we are more in the 10 to 20 range. Since we have big pipes and good servers, I don't expect latency to be much of a problem no matter what we do.

Thanks for your input.

Randy

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.
Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating ODBC link within SELECT statement

Is this possible. To explain myself, here is my situation. I created an
Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
anyone to use this file on their PC, that same ODBC link must be setup. Is
their some code I can use within a Select Statement that will allow me to
bypass having to create an ODBC on everyone's PC?
Thanks.Hi Preacher Man,
You've cross-posted this to a bunch of newsgroups, some of which, for
example microsoft.public.fox.vfp.queries-sql, don't even apply to your
question. What you haven't done is post to a relevant Excel newsgroup. Try
posting this question (and your other one) there.
Someone will probably tell you that you can use a connection string or a
DSN-less connection as described here:
http://support.microsoft.com/kb/q165866/ . (GoogleGroups helped me find
that.)
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@.msn.com www.cindywinegarden.com
"Preacher Man" <nospam> wrote in message
news:uFEmV7eJGHA.3936@.TK2MSFTNGP10.phx.gbl...
> Is this possible. To explain myself, here is my situation. I created an
> Excel Spreadsheet that uses an ODBC link to a SQL database. In order for
> anyone to use this file on their PC, that same ODBC link must be setup.
> Is their some code I can use within a Select Statement that will allow me
> to bypass having to create an ODBC on everyone's PC?
> Thanks.
>

Creating join statements

I have the following situation. We sell books on our website, and some
of the books have more than one author. So I needed to create a
many-to-many table, which is the intermidiate table between the author
table and the book table.

I can't get the right join statement to work. I've used the code below,
submitting an isbn (book id number) to identify the book, but the return
from the query simply sends me back all of the authors that are in the
many_to_many table
(called the book_to_author table here). I'd like it to return only the
authors attached to that isbn, instead of all the authors that are in
that table. What's wrong with the code below? Thanks for your help!

SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
Expr3 FROM author a INNER JOIN book_to_author m ON
a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
bf.isbn='"&isbn&"' order by m.id desc

Bill

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Do the following:
Your inner join statement should be:INNER JOIN book_to_author m ON
a.author_id= book_to_author.author_id

--
__________________________________________________ _________________
Remotely manage MS SQL db with SQLdirector -
www.ciquery.com/tools/sqldirector/

"Bill" <BillZimmerman@.gospellight.com> wrote in message
news:3f7b2243$0$24217$75868355@.news.frii.net...
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc
> Bill
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Bill (BillZimmerman@.gospellight.com) writes:
> I have the following situation. We sell books on our website, and some
> of the books have more than one author. So I needed to create a
> many-to-many table, which is the intermidiate table between the author
> table and the book table.
> I can't get the right join statement to work. I've used the code below,
> submitting an isbn (book id number) to identify the book, but the return
> from the query simply sends me back all of the authors that are in the
> many_to_many table
> (called the book_to_author table here). I'd like it to return only the
> authors attached to that isbn, instead of all the authors that are in
> that table. What's wrong with the code below? Thanks for your help!
> SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
> bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id AS
> Expr3 FROM author a INNER JOIN book_to_author m ON
> a.author_id=m.author_id CROSS JOIN book_detail_final bf WHERE
> bf.isbn='"&isbn&"' order by m.id desc

That CROSS JOIN is most certainly not what you like. (author JOIN
book_to_author) gives your all authors and the books they have
written. Then you form a cartesian product with the book_detail_final
table, and finally you restrict the book with that isbn. But there
is no connection with the first two tables.

It is possible that this query cuts it:

SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,
bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id,
m.author_id AS Expr3
FROM author a
JOIN book_to_author m ON a.author_id = m.author_id
JOIN book_detail_final bf ON bf.isbn = m.isbn
WHERE bf.isbn='"&isbn&"'
ORDER BY m.id DESC

But since I don't know your tables, this is a guess.

If the guess is wrong, please post the following:

o CREATE TABLE statements of your tables.
o INSERT statements with sample data.
o The desired given from the sample data.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp