Table Join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Join

Author

Topic

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-31 : 07:08:19

CREATE TABLE [dbo].[App_Main](
[EAI_Code] [varchar](4) NOT NULL,
[App_Short_Name] [varchar](50) NULL,
[LOB] [varchar](50) NULL,
[App_Status] [varchar](20) NULL,
[App_Class] [varchar](50) NULL,
[Critical_Ind] [char](1) NULL,
[Master_App_Id] [varchar](10) NULL,
[Master_App_Name] [varchar](100) NULL,
[App_Long_Name] [varchar](100) NULL,
[Active_Ind] [varchar](1) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[App_Test](
[EAI Code] [varchar](4) NULL,
[App Name] [varchar](90) NULL,
[OBM Status] [varchar](50) NULL,
[OBM Target Date] [datetime] NULL,
[OBM Comments] [varchar](500) NULL,
[L2 Group] [varchar](50) NULL,
[L2 Subb Group] [varchar](50) NULL,
[PM L2 Lead] [varchar](50) NULL,
[Platform] [varchar](50) NULL,
[Critical App] [varchar](50) NULL,
[Tricare] [varchar](50) NULL
) ON [PRIMARY]

GO

I have to join these two tables, where EAI code is identical. Can anyone help me with the query.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 07:14:46

Isn't it as simple as this?

select colums from app_Main as main inner join app_test as test on main.[EAI code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-31 : 07:22:20

Yes

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-31 : 07:35:39

quote:
Originally posted by madhivanan

Isn't it as simple as this?

select colums from app_Main as main inner join app_test as test on main.[EAI code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

EAI Code column is displayed twice

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:30:01

quote:
Originally posted by vignesht50

quote:
Originally posted by madhivanan

Isn't it as simple as this?

select colums from app_Main as main inner join app_test as test on main.[EAI code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

EAI Code column is displayed twice

show some sample data from tables and explain what you want as output/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-31 : 08:44:50

quote:
Originally posted by visakh16

quote:
Originally posted by vignesht50

quote:
Originally posted by madhivanan

Isn't it as simple as this?

select colums from app_Main as main inner join app_test as test on main.[EAI code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

EAI Code column is displayed twice

show some sample data from tables and explain what you want as output/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

I use this query to fetch from table columns. But the EAI Code column displays twice in the table and it should be displayed only once since the column in both tables has same values.

select *from App_Main as main inner join App_Test as test on main.[EAI_Code]=test.[EAI code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-31 : 08:47:58

see how to post a question with proper data below

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Without seeing any data whatever you say makes not much sense to us!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-10-31 : 09:46:09

You have used * which will select all the columns from all the tables involved in JOIN. Specify the columns

select main.[EAI_Code], main.othercols, test.some_col, etc from App_Main as main inner join App_Test as test on main.[EAI_Code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-10-31 : 10:45:30

Yeah got it. Thanks a lot Madhi!

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-01 : 01:47:39

quote:
Originally posted by madhivanan

You have used * which will select all the columns from all the tables involved in JOIN. Specify the columns

select main.[EAI_Code], main.othercols, test.some_col, etc from App_Main as main inner join App_Test as test on main.[EAI_Code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

With this query I am going to bind these values to a gridview in asp.net. How can I possibly write an update and delete query since I do joins here.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2013-11-01 : 06:51:56

I don't think you can do update and delete on this. However read about INSTEAD OF TRIGGERs

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-01 : 13:18:00

quote:
Originally posted by vignesht50

quote:
Originally posted by madhivanan

You have used * which will select all the columns from all the tables involved in JOIN. Specify the columns

select main.[EAI_Code], main.othercols, test.some_col, etc from App_Main as main inner join App_Test as test on main.[EAI_Code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

With this query I am going to bind these values to a gridview in asp.net. How can I possibly write an update and delete query since I do joins here.

update and delete on which table data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

vignesht50
Yak Posting Veteran

82 Posts

Posted - 2013-11-04 : 01:44:21

quote:
Originally posted by visakh16

quote:
Originally posted by vignesht50

quote:
Originally posted by madhivanan

You have used * which will select all the columns from all the tables involved in JOIN. Specify the columns

select main.[EAI_Code], main.othercols, test.some_col, etc from App_Main as main inner join App_Test as test on main.[EAI_Code]=test.[EAI code]

Madhivanan

Failing to plan is Planning to fail

With this query I am going to bind these values to a gridview in asp.net. How can I possibly write an update and delete query since I do joins here.

update and delete on which table data?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Update & Delete query is going to happen in App_Test table

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-04 : 02:29:45

then best thing would be create a procedure for doing update/delete and call it from asp.net

http://support.microsoft.com/kb/306574

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources