SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Table Join
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/31/2013 :  07:08:19  Show Profile  Reply with Quote
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

India
22772 Posts

Posted - 10/31/2013 :  07:14:46  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/31/2013 :  07:22:20  Show Profile  Reply with Quote
Yes
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/31/2013 :  07:35:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/31/2013 :  08:30:01  Show Profile  Reply with Quote
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
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/31/2013 :  08:44:50  Show Profile  Reply with Quote
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]

Edited by - vignesht50 on 10/31/2013 08:45:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/31/2013 :  08:47:58  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 10/31/2013 :  09:46:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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

Edited by - madhivanan on 10/31/2013 09:46:58
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 10/31/2013 :  10:45:30  Show Profile  Reply with Quote
Yeah got it. Thanks a lot Madhi!
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 11/01/2013 :  01:47:39  Show Profile  Reply with Quote
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.

Edited by - vignesht50 on 11/01/2013 02:02:49
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22772 Posts

Posted - 11/01/2013 :  06:51:56  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/01/2013 :  13:18:00  Show Profile  Reply with Quote
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
Go to Top of Page

vignesht50
Yak Posting Veteran

82 Posts

Posted - 11/04/2013 :  01:44:21  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 11/04/2013 :  02:29:45  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000