Select command with multiple tables -- sql server (alternate)

Shwetabh
2/14/2006 12:59:04 AM

Hi,
I have two tables: Code and Color.
The create command for them is :

create table Color(
Partnum varchar(10),
Eng_Color char(10),
Span_Color char(20),
Frch_Color char(20),
CONSTRAINT pkPartnum PRIMARY KEY(Partnum)
)

create table Code
(
Partnum varchar(10),
Barcode varchar(11),
I2of5s varchar(13),
I2of5m varchar(13),
UPC varchar(11),
BigboxBCode varchar(11),
DrumBCode varchar(11),
TrayBCode varchar(11),
QtyBCode varchar(11),
CONSTRAINT fkPartnum FOREIGN KEY(Partnum) references Color(Partnum)
)

Now my question is,
how can i give a select statement such that I can get all the fields as
output.
Also plz note that the above is a sample. I have another 9 tables and I
need a solution
such that on being refered by Partnum, I can get all the attributes.

Thanks

David Portas
2/14/2006 1:25:30 AM

[quoted text, click to view]

I guess you'll want an inner join. You can read about types of joins in
Books Online. For example:

SELECT
D.partnum, D.barcode, D.i2of5s, D.i2of5m, D.upc, D.bigboxbcode,
D.drumbcode, D.traybcode, D.qtybcode,
C.eng_color, C.span_color, C.frch_color
FROM Color AS C
JOIN Code AS D
ON C.partnum = D.partnum ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Jens
2/14/2006 1:30:00 AM

Dpending that you are using a stored procedure Selecting the tables
within this, you can do through the resutset selecting every single
resultset as a table. Depending on your coding language there should be
masses of examples out there, butyou didn=B4t posted that information
about your coding enviroment.

HTH, Jens Suessmeyer.

Shwetabh
2/14/2006 1:46:46 AM

Hi,
I am using Visual Basic 6 as frontend and MS SQL SERVER 2000 as
backhand.
Actually I am workin gon a converter which will convert legacy database
in DBF to SQL database.
I hope this info helps

Shwetabh
2/14/2006 1:53:37 AM

[quoted text, click to view]

That's one way of doing it. But since i am using more than two tables,
it becomes difficult to write each and every field in the query. Is
there
any query like "Select * from ..." which can do the job?

David Portas
2/14/2006 2:37:11 AM

[quoted text, click to view]

Certainly you can use SELECT * but putting SELECT * in production code
is sloppy, inefficient and in the longer term can prove unreliable and
costly to maintain. Best practice is to list all the column names.

If you want to save some typing then use the Object Browser in Query
Analyzer or Management Studio. You can click and drag the column list
into your queries with no typing required.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Shwetabh
2/14/2006 2:44:40 AM

[quoted text, click to view]

But the problem is that this database has to be accessed by a third
party
application which will need to access the required data using Partnum
as
keyword. In such scenario, it becomes neccessary to use select * from
....
to get the row. How can I help it in such a case?
Also, will it be inefficient to use select * from .. if we have to
retrieve just 1
record or is it inefficient if more records have to be retrieved?

David Portas
2/14/2006 3:31:55 AM

[quoted text, click to view]

Why would it be necessary to use SELECT * in such a scenario? SELECT *
is slow because it requires extra work by the server to retrieve the
column metadata. It's unreliable because more code may break if and
when the table structure changes. It's hard to maintain because you
can't easily search for column dependencies in your code during
development.

In another post you stated your application was VB so you should be
able to create a stored procedure and call that from your VB code.
Again, it's poor practice to put SQL code directly into applications if
you can possibly avoid it.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Erland Sommarskog
2/14/2006 11:12:34 PM

Shwetabh (shwetabhgoel@gmail.com) writes:
[quoted text, click to view]

Why would have you to use SELECT *?

The problem with SELECT * is that it causes a maintenance problem.
You add a colunm, maybe in the middle. Oops, the client did handle
column numbers, and now gets confused. You remove a column, but the
query does not break. But client does.

You should never include more columns in your queries than are are
actually needed. Believe me. I work with a database that has a long
history, and since this still is very much a vital product, we change
the data model to support new features. One problem I often face is
whether a certain column can be dropped or redefined. I can make a
search in which stored procedures it is used, but often I end up in
some general procedure where data goes into the client, or even worse
are exposed in a general API. In many cases, it does not seem to make
sense, and it smells that someone added all columns while he was
at it.

[quoted text, click to view]

The ineffeciency lies in the fact that you may bring bytes over the
wire that no one cares about. There is also a cost for expanding the
* into column names, but that cost is like to be negligible in many
cases.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Shwetabh
2/15/2006 8:43:49 PM

[quoted text, click to view]

I understand what you are saying. But consider this scenario:
A user needs a few more fields in the database and adds them to a table
kept for the purpose. Now how can _those_ fields be accessed without
using select *?

Erland Sommarskog
2/16/2006 12:00:00 AM

Shwetabh (shwetabhgoel@gmail.com) writes:
[quoted text, click to view]

Changing the schema in a database is usually not something you do out of a
whim, and normally by a DBA, and not just any user.

And if a new field is added - how can you know off-hand that it is of
interest to you?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Shwetabh
2/16/2006 12:53:18 AM

[quoted text, click to view]

Well, apparently, since the dataabse will be used for Label printing,
the DBA has to
change the schema by adding new fields according to the requirements.
Do you suggest instead of normalising the database I should create a
single table
which holds all the fields which will be used for label? Or is thr some
better way out.
Now, since I am converting legacy database, it should be possible to
add data to the table through a single query which I cannot really
foresee happening in a normalised database.
What do u suggest?

Hugo Kornelis
2/16/2006 8:51:24 PM

[quoted text, click to view]

Hi Shwetabh,

Easy.

In the parts of the application that have to use the new column, you can
add it to the column list in the SELECT statement at the same time
you're making the change to use the column.

And in the parts of the application that don't have to use the new
column, you don't have to change anything.

--

Erland Sommarskog
2/16/2006 10:16:13 PM

Shwetabh (shwetabhgoel@gmail.com) writes:
[quoted text, click to view]

Since I don't have the full story, but just some selected bits and pieces,
maybe I should not suggest anything.

Generally, SELECT * is bad. You talk about converting a legacy database,
but I don't know if that database is even SQL Server. And how your
question about relational database fits into this, I don't really see.

If you could give a more complete picture of what you are up to, it
might be easier to give better advice.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Shwetabh
2/16/2006 11:34:11 PM

[quoted text, click to view]

Ok,here are the details:

I am supposed to convert DBASE IV data to MS SQL server 2000 database.
Now, there are about 40 DBASE tables each of which contains fields
ranging
from 3 to 30. Most of the fields hold either NULL values or redundant
data. In all these tables and fields, there is only one common field
"Partnum".
Now earlier, this DBASE database was used by a labelling software
"Easylabel". It used to
retrieve the neccessary data using the field "Partnum". This software
will connect to
SQL server and retrieve the data when conversion process is complete.

Now my dillema is that should I just create a database containing all
the fields in a single
table or should I try to create relationships among different data
objects. If I put them in a single table, will the system take a
performance hit when accessing data?
If I create relationships, how can I retrieve the data from tables
where information about new fields added by users without using select
*..?

I hope I made myself clear. If any doubts, plz let me know.

Erland Sommarskog
2/17/2006 10:46:29 PM

Shwetabh (shwetabhgoel@gmail.com) writes:
[quoted text, click to view]

So you are saying that the application will largely be unchanged, only
the database will change? That puts quite some restriction on the work.
It would of course be nicer to work with a clean sheet and do it right.

Since I don't know the application, it is difficult to say "do this"
or "do that". But I think that you design a solution with maintainability
and extensibilty foremost in mind. I don't think one single table is
the answer to that aim. But, again, I don't know the business domain.

As for performance, what data volumes are we talking about?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Shwetabh
2/21/2006 8:01:51 PM

[quoted text, click to view]

I am working on a software to manage the labelling database. The DBASE
IV database tables are to be converted to MS SQL server database. Each
table consists of around 45 to 60 records.There is only one field which
is common in all tables and is unique. I will be using that field as
the primary key. Would I be wrong if I create a database in SQL and add
all DBASE IV tables according to the names of the files they are stored
in?
I can send you the sample database if needed.

As for extensibility, since the database will be stored in seperate
tables in SQL server database, I think there should be no problem. Am I
correct in my assumption?
Awaiting your replies,
Thanks

David Portas
2/22/2006 5:14:12 AM

[quoted text, click to view]

You don't need to worry about performance. The database schema alone
isn't going to make a measurable difference to performance if your
tables average just 45-60 rows. Not unless they are likely to grow 100s
or 1000s of times bigger in the lifetime of the system.

Design a schema that will preserve data integrity and be easy to
maintain in future. Usually that means the schema should be fully
normalized but if this is just a one-off, low-value project you may
prefer to mimic the existing structure in SQL in order to minimise
changes to the application.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Erland Sommarskog
2/22/2006 10:47:52 PM

Shwetabh (shwetabhgoel@gmail.com) writes:
[quoted text, click to view]

As David said, with those volumes there is no reason to consider a "smart"
design for performance. As I said, focus on maintainability and
extensibility. And ease of development. That may be a single-table
design, but most likely it's a more normalised design.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

sql server (alternate) : Select command with multiple tables