getting two query results into 1 table

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

getting two query results into 1 table

Author

Topic

jaskalirai
Starting Member

31 Posts

Posted - 2007-11-29 : 09:13:31

SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
FROM dbo.S_PATIENTS
WHERE (PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL)

SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
FROM dbo.S_PATIENT_IDS LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE (dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N')

i want to select the results from the querys into one table how would i do this ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-29 : 09:18:19

Query1
union all
Query2

Madhivanan

Failing to plan is Planning to fail

jaskalirai
Starting Member

31 Posts

Posted - 2007-11-29 : 09:25:00

SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
into eof.dbo.newtable
FROM dbo.S_PATIENTS
WHERE (PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL)

union all

SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
into eof.dbo.newtable
FROM dbo.S_PATIENT_IDS LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE (dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N')

this isnt working and when i try

SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
into eof.dbo.newtable
FROM dbo.S_PATIENTS
WHERE (PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL)

union all

SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
FROM dbo.S_PATIENT_IDS LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE (dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N')

the new table only has the first 3 columns

any ideas ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-29 : 09:29:30

Do you mean that you want 6 columns? If so, then how would the two different selects join together? What is the common field?

jaskalirai
Starting Member

31 Posts

Posted - 2007-11-29 : 09:31:37

common field is patnt_refno any ideas? yes i want to display all 6 columns

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-29 : 15:51:22

quote:
Originally posted by jaskalirai

SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG
/snip
union all

SELECT dbo.S_PATIENT_IDS.END_DTTM, dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
/snip

the new table only has the first 3 columns

any ideas ?

Yes. Learn to use SQL.

For a union to succeed, all queries must return the same number of columns, and the datatype of each column must be the same. So your first query returns three columns of whatever datatype (int?), and your second returns three columns of whatever datatype (datetime, int, varchar?).

So, of course you only get three columns back. You've only asked for three columns. SQL Server is doing the automatic conversions between data types for you, because it just coincidentally can.

If you're looking for all six columns, you're wanting a JOIN instead of a UNION. What field is common between the two tables to match the records from one table with records in the other?

If there is no common field to match them (which means your query wouldn't make any sense, AFAIK), you can join with a UNION ALL (untested):


SELECT PATNT_REFNO, PATNT_REFNO_NHS_IDENTIFIER, ARCHV_FLAG,
 '', '', ''
FROM dbo.S_PATIENTS
WHERE PATNT_REFNO_NHS_IDENTIFIER IS NOT NULL
UNION ALL
SELECT '', '', '', dbo.S_PATIENT_IDS.END_DTTM, 
dbo.S_PATIENT_IDS.PITYP_REFNO, dbo.S_REFERENCE_VALUES.DESCRIPTION
FROM dbo.S_PATIENT_IDS
LEFT OUTER JOIN
dbo.S_REFERENCE_VALUES ON 
dbo.S_PATIENT_IDS.PITYP_REFNO = dbo.S_REFERENCE_VALUES.RFVAL_REFNO
WHERE dbo.S_PATIENT_IDS.ARCHV_FLAG = 'N'

However, as I said, unioning the two tables together with no common field to connect rows makes no sense to me.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-29 : 16:06:29

Which other table has PATNT_REFNO? Is it S_PATIENT_IDS or S_REFERENCE_VALUES or both?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources