Query Help - 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

Query Help

Author

Topic

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 11:46:23

If i run three seperate queries is there any way I can combine the results into one table if they have a like column? (The column has a different name but the data within it is identical)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-20 : 11:48:25

Sure. UNION and UNION ALL do just that. http://msdn.microsoft.com/en-us/library/ms180026.aspx

Edit: That assumes that all your queries have the same columns. If they don't, then you can join the results of the queries. Can you post some sample data?

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 12:40:21

show us the queries

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 12:44:02

Select * from enc
Select * from entid

This is what I run to get the two results... really whati I would like is to combine those into one query ... but if not then I need to do what I asked originaly.. thanks for all your help

quote:
Originally posted by X002548

show us the queries

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 12:46:00

I can not get the union to work ... im not sure what i am missing

quote:
Originally posted by X002548

show us the queries

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 12:56:21

Select 'ENC' AS SOURCE, * from enc
UNION ALL
Select 'ENTID' AS SOURCE, * from entid

Do the tables have the same number of columns? Do the corresponding columns have the same data type?

Post the DDL of the 2 tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 12:59:37

quote:
Originally posted by X002548

Select 'ENC' AS SOURCE, * from enc
UNION ALL
Select 'ENTID' AS SOURCE, * from entid

Do the tables have the same number of columns? Do the corresponding columns have the same data type?

Post the DDL of the 2 tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 13:00:37

ummm..yes?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 13:06:49

Ok how they do not have the same number of fields ... i might could narrow that down tho ... and I would post a screen shot and an example if i could figure out how.

quote:
Originally posted by allenm

quote:
Originally posted by X002548

Select 'ENC' AS SOURCE, * from enc
UNION ALL
Select 'ENTID' AS SOURCE, * from entid

Do the tables have the same number of columns? Do the corresponding columns have the same data type?

Post the DDL of the 2 tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 13:13:13

Do you have SQL Server Client tools installed? Do you know what Books online is?

Do you need to say SELECT *? That's REALLY Bad form anyway, except for testing stuff out

Do you know how to script out the DDL for your tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 13:14:16

From Books Online

quote:

The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets that are combined by using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types. For more information, see Guidelines for Using Union.

EDIT: Just List out in the SELECT THe Columns you need. Make sure they are both in the same order as each other and their dadatypes are consistent

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 13:20:40

I am using sql managment studio and no i dont know how to script out the ddl since im not sure what that is lol.... my main goal is i have two different table one being address and one bing names ... the both share a common id column ... so what i need to is to take those tables and comine them by id so i have a name with an address

Thanks so much this is very important,
Allen

quote:
Originally posted by X002548

Do you have SQL Server Client tools installed? Do you know what Books online is?

Do you need to say SELECT *? That's REALLY Bad form anyway, except for testing stuff out

Do you know how to script out the DDL for your tables?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 13:28:25

Right Click on the Database

Choose Tasks>Generate Scripts

Choose Select Specific Database Objects

Click the Plus Sign next to Tables

SELECT the 2 Tables you have Posted here

Click Next

At the bottom click Save to New Query Window

Click Next and Next again

Then Post that Here

Use [ code] [ /code] tags (with out the leading space) to wrap around the DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 13:40:44

Ok I just tried this and I dont have "choose specific database objects"

I will keep at this till I get it

quote:
Originally posted by X002548

Right Click on the Database

Choose Tasks>Generate Scripts

Choose Select Specific Database Objects

Click the Plus Sign next to Tables

SELECT the 2 Tables you have Posted here

Click Next

At the bottom click Save to New Query Window

Click Next and Next again

Then Post that Here

Use [ code] [ /code] tags (with out the leading space) to wrap around the DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 14:10:56

Did you at least get to Tasks?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 14:12:32

Yessir I got that far... also this table contains alot of personal data lol will this generate something I should not upload?

Thanks

quote:
Originally posted by X002548

Did you at least get to Tasks?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 14:16:03

We are not asking for the data, just the structure

You know

CREATE TABLE [ENC] (
Col1 int,
Col2 varchar(10),
ect

That's DDL

So if you got to tasks..did you see generate scripts?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 14:17:05

Almost Heaven....

Went to WVU myself...morning shower bon...um never mind

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

allenm
Starting Member

11 Posts

Posted - 2012-03-20 : 14:21:49

Awesome .. you went to wvu?

And here is what that generated

USE [PwHcPatient]
GO
/****** Object: Table [dbo].[Enc] Script Date: 03/20/2012 14:20:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Enc](
[AdmSys] [int] NOT NULL,
[ClnDta] [char](1) NULL,
[EpsTyp] [varchar](2) NULL,
[FrpRel] [varchar](2) NULL,
[OrgSys] [int] NULL,
[FrpSys] [int] NULL,
[PatSys] [int] NULL,
[BegDat] [datetime] NULL,
[EndDat] [datetime] NULL,
[RefDat] [datetime] NULL,
[RefSrc] [int] NULL,
[EpsID] [varchar](20) NULL,
[RefCat] [varchar](10) NULL,
[BegEtrDat] [datetime] NULL,
[EndEtrDat] [datetime] NULL,
[EmpRelFlg] [char](1) NULL,
[FstEpsFlg] [char](1) NULL,
[ExtEpsID] [varchar](20) NULL,
[ExtOrdNum] [varchar](20) NULL,
[HspFlg] [char](1) NOT NULL DEFAULT ('N'),
[BrvFlg] [char](1) NOT NULL DEFAULT ('N'),
[RefCtc] [int] NULL,
[LedFlg] [char](1) NOT NULL DEFAULT ('N'),
CONSTRAINT [PK__Enc__77F4D5BB] PRIMARY KEY CLUSTERED
(
[AdmSys] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[EntID] Script Date: 03/20/2012 14:20:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EntID](
[EntSys] [int] NOT NULL,
[ExtID] [varchar](20) NULL,
[Typ] [char](1) NULL,
[Cat] [varchar](10) NULL,
[ID] [varchar](20) NULL,
[NamStr] [varchar](130) NULL,
[LstNam] [varchar](35) NULL,
[FstNam] [varchar](35) NULL,
[MidNam] [varchar](35) NULL,
[Pre] [varchar](10) NULL,
[Suf] [varchar](10) NULL,
[OrgSys] [int] NULL,
[ActSts] [char](1) NULL,
[EtrDat] [datetime] NULL,
[EntPrsNum] [varchar](20) NULL,
[PsuPrsInd] [varchar](1) NOT NULL CONSTRAINT [DF__EntID__PsuPrsInd__7B113988] DEFAULT ('N'),
[EmpIDNum] [varchar](10) NULL,
[Spc] [varchar](20) NULL,
[NPI] [varchar](15) NULL,
[HspEmp] [char](1) NOT NULL CONSTRAINT [DF__EntID__HspEmp__3DDE0E16] DEFAULT ('N'),
[EDIAccNum] [varchar](40) NULL,
PRIMARY KEY CLUSTERED
(
[EntSys] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Check [EntID_HspEmp_IS_YN] Script Date: 03/20/2012 14:20:43 ******/
ALTER TABLE [dbo].[EntID] WITH NOCHECK ADD CONSTRAINT [EntID_HspEmp_IS_YN] CHECK (([HspEmp] = 'Y' or [HspEmp] = 'N'))
GO
ALTER TABLE [dbo].[EntID] CHECK CONSTRAINT [EntID_HspEmp_IS_YN]
GO
/****** Object: Check [CK_Enc_BrvFlg] Script Date: 03/20/2012 14:20:53 ******/
ALTER TABLE [dbo].[Enc] WITH CHECK ADD CONSTRAINT [CK_Enc_BrvFlg] CHECK (([BrvFlg] = 'N' or [BrvFlg] = 'Y'))
GO
ALTER TABLE [dbo].[Enc] CHECK CONSTRAINT [CK_Enc_BrvFlg]
GO
/****** Object: Check [CK_Enc_HspFlg] Script Date: 03/20/2012 14:20:53 ******/
ALTER TABLE [dbo].[Enc] WITH CHECK ADD CONSTRAINT [CK_Enc_HspFlg] CHECK (([HspFlg] = 'Y' or [HspFlg] = 'N'))
GO
ALTER TABLE [dbo].[Enc] CHECK CONSTRAINT [CK_Enc_HspFlg]
GO
/****** Object: Check [CK_Enc_LedFlg] Script Date: 03/20/2012 14:20:53 ******/
ALTER TABLE [dbo].[Enc] WITH CHECK ADD CONSTRAINT [CK_Enc_LedFlg] CHECK (([LedFlg]='N' OR [LedFlg]='Y'))
GO
ALTER TABLE [dbo].[Enc] CHECK CONSTRAINT [CK_Enc_LedFlg]
GO

quote:
Originally posted by X002548

Almost Heaven....

Went to WVU myself...morning shower bon...um never mind

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources