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

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/ms130214(en-US,SQL.90).aspx
--

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.

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

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


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?

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/ms130214(en-US,SQL.90).aspx
--

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

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?

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/ms130214(en-US,SQL.90).aspx
--

Shwetabh (shwetabhgoel@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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

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