Joining 2 in-memory tables - 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

Joining 2 in-memory tables

Author

Topic

Shaper
Starting Member

8 Posts

Posted - 2009-03-25 : 01:16:59

Hi there,

I have 2 select queries which create 2 tables which I then want to join into a single table.

My first query is:

SELECT core_relationship_concept_id_1, core_relationship_concept_id_2
FROM iaso_core_relationships
WHERE core_relationship_concept_id_2 = 160845005
AND core_relationship_type = 160939001
AND core_relationship_load = 51

resulting in:

102520001 160845005
102517009 160845005
160682003 160845005
160683008 160845005
160684002 160845005
160685001 160845005
160686000 160845005

My second query is:

SELECT core_relationship_concept_id_1, core_relationship_concept_id_2
FROM iaso_core_relationships
WHERE core_relationship_concept_id_2 = 102509001
AND core_relationship_type = 116680003
AND core_relationship_load = 51

resulting in:

102520001 102509001
102517009 102509001
160675002 102509001
160674003 102509001
160683008 102509001
160684002 102509001
160685001 102509001
160677005 102509001

What I want is to then get the list of values which appear in the left hand column of both tables i.e:

102520001
102517009
160683008
160684002

How do I pack both selects into one SQL statement and then join them to get the above 4 data values?

Shaper
Starting Member

8 Posts

Posted - 2009-03-25 : 01:58:11

Durr, I think I've solved it with:

SELECT core_relationship_concept_id_1
FROM iaso_core_relationships
WHERE core_relationship_concept_id_2 = 160845005
AND core_relationship_type = 160939001
AND core_relationship_load = 51

INTERSECT

SELECT core_relationship_concept_id_1
FROM iaso_core_relationships
WHERE core_relationship_concept_id_2 = 102509001
AND core_relationship_type = 116680003
AND core_relationship_load = 51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources