467,075 Members | 900 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,075 developers. It's quick & easy.

Select command with multiple tables

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

Feb 14 '06 #1
  • viewed: 2641
Share:
19 Replies
Shwetabh wrote:
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


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/m...S,SQL.90).aspx
--

Feb 14 '06 #2
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īt posted that information
about your coding enviroment.

HTH, Jens Suessmeyer.

Feb 14 '06 #3
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

Feb 14 '06 #4

David Portas wrote:
Shwetabh wrote:
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


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/m...S,SQL.90).aspx
--

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?

Feb 14 '06 #5
Shwetabh wrote:

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?


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/m...S,SQL.90).aspx
--

Feb 14 '06 #6

David Portas wrote:
Shwetabh wrote:

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?


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/m...S,SQL.90).aspx
--


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?

Feb 14 '06 #7
Shwetabh wrote:

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?


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/m...S,SQL.90).aspx
--

Feb 14 '06 #8
Shwetabh (sh**********@gmail.com) writes:
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?
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.
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?


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, es****@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
Feb 14 '06 #9

David Portas wrote:
Shwetabh wrote:

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?


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/m...S,SQL.90).aspx
--


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 *?

Feb 16 '06 #10
Shwetabh (sh**********@gmail.com) writes:
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 *?


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, es****@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
Feb 16 '06 #11

Erland Sommarskog wrote:
Shwetabh (sh**********@gmail.com) writes:
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 *?


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, es****@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


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?

Feb 16 '06 #12
On 15 Feb 2006 20:43:49 -0800, Shwetabh wrote:
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.
(snip)
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 *?


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.

--
Hugo Kornelis, SQL Server MVP
Feb 16 '06 #13
Shwetabh (sh**********@gmail.com) writes:
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?


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, es****@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
Feb 16 '06 #14

Erland Sommarskog wrote:
Shwetabh (sh**********@gmail.com) writes:
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?


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, es****@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


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.

Feb 17 '06 #15
Shwetabh (sh**********@gmail.com) writes:
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 *..?


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, es****@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
Feb 17 '06 #16

Erland Sommarskog wrote:
Shwetabh (sh**********@gmail.com) writes:
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 *..?


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, es****@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


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

Feb 22 '06 #17
Shwetabh wrote:
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


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/m...S,SQL.90).aspx
--

Feb 22 '06 #18
Shwetabh (sh**********@gmail.com) writes:
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?


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, es****@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
Feb 22 '06 #19
as a place to start, create a sql table for every dbf file. copy the
column names verbatim.

then use the import function to import the data.

no matter WHAT you eventually end up with, you will have to do this to
get all the data in.

from there, you will have learned a lot, and will think of ways to
change the app to make it better.

but, to get all the data in, you have to do the first step.

Feb 26 '06 #20

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Ahmet Karaca | last post: by
3 posts views Thread by Joe via DotNetMonster.com | last post: by
1 post views Thread by Diffident | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.