I want to select all fields from a table but there are three tables after FROM

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

In the following Stored Procedure I want to select just all fields of the Med table and I want not to separate its field names, I want to use the Stored Procedure like this without selecting the fields of non required tables. Is there a way to do that?

Thanks.

USE [pharm lastver]
GO
/****** Object:  StoredProcedure [dbo].[SP_Med_SelectAllMed_ByActiveID]    Script Date: 05/14/2011 22:22:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
craete proc [dbo].[SP_Med_SelectAllMed_ByActiveID]
@P_Activemat_ID int
as
begin
select * from Med,Active_material,Active_mat_med
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Active_material.Activemat_ID=@P_Activemat_ID
end

Posted 15-May-11 10:42am

Yasser El Shazly

Updated 15-May-11 10:51am

Fabio V Silva

v2

Add a Solution

Comments

Fabio V Silva 15-May-11 16:51pm

Edited formatting.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Pete O'Hanlon · Answer 1 · 2011-05-15T11:13:00

You need to explicitly select the columns. Say you had the fields id, name and active in the table Med, the start of your query becomes:

SQL

select id, name, active from

It is generally good practice to do this as you pick the fields that you want to retrieve and don't retrieve the ones you want. There's normally one or two fields you don't want returning, and selecting them is wasteful of resources.

Bryian Tan · Answer 2 · 2011-05-15T11:24:00

hello,

If you want to select all the column in the med table, just replace * with Med.* or you can use Med.column1, Med.column2, Med.column3, etc...

SQL

select med.* from Med,Active_material,Active_mat_med
where Med.Med_ID=Active_mat_med.Med_ID
and Active_material.Activemat_ID=Active_mat_med.Activemat_ID
and Active_material.Activemat_ID=@P_Activemat_ID

I want to select all fields from a table but there are three tables after FROM

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0