Select columns from multiple tables without Cartes

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

Select columns from multiple tables without Cartes

Author

Topic

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 04:25:47

Hi,

I have two database tables. I need to select specific columns from both tables with out performing a Cartesian product.

The result should be distinct values for all columns.

I tried the following query –

SELECT t1. col1, t1.col2, t1.col3, t2.col1, t2.col2
FROM table1 t1, table2 t2
WHERE ...

Please suggest.

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2011-08-30 : 05:36:58

what is the requirement why do you want to do this.?
post some more details like sample data and expected results.

Karthik
http://karthik4identity.blogspot.com/

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 06:14:45

Hi Karthik,

I want to fetch data from different tables and populate all that in a single excel sheet.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-30 : 06:37:07

You need to join the tables on a common key. You need to follow the ULTRA BASIC links in my signature.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 10:06:07

Hi,

I tried to join the tables on a common key as follows :

SELECT t1. col1, t1.col2, t1.col3, t2.col1, t2.col2
FROM table1 t1, table2 t2
WHERE t1.col4=t2.col4

But this still shows the same result.
I think the problem here is that both the tables have only one column in common (say col4). This column has same values for all the selected rows.
Is there any workaround. All I want is to fetch some columns from 2 tables in one common place.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-08-30 : 10:09:18

Follow the HOW TO ASK link in my signature. That will show you how to give us the data we need to help answer your question.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:09:40

something like below should give you what you're looking for


SELECT t1. col1, t1.col2, t1.col3, t2.col1, t2.col2
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY col4 ORDER BY col4) AS Seq,* FROM table1) t1
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY col4 ORDER BY col4) AS Seq,* FROM table2) t2
ON t1.col4 = t2.col4
AND t1.Seq = t2.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 10:17:30

Thanks Visakh.

It did work but only for one row.
What I want is to show selected column data from both tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 10:21:19

what do you mean by one row? can you explain?
or do you mean this?


SELECT t1. col1, t1.col2, t1.col3, t2.col1, t2.col2
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY col4 ORDER BY col4) AS Seq,* FROM table1) t1
FULL JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY col4 ORDER BY col4) AS Seq,* FROM table2) t2
ON t1.col4 = t2.col4
AND t1.Seq = t2.Seq

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 10:55:33

Hi Vishakh,

I have 2 tables as follows:

UTable
-------------
VERSIONID TABLENAME UEXTENSION OWNER ALIAS OBJ_BH_REL ELEM_BH_REL INHERITANCE ORDERNRO
----------------------------------------------------------------------
DC_H:((69)) DIM_H _RNC ALL DC 0 0 0 0.00E+00
DC_H:((69)) DIM_H _DST_TZ_NODEB ALL DC 0 0 0 2
DC_H:((69)) DIM_H _UCELL ALL DC DIM_H _UCELL_NEIGHBOUR 0 0 0 4
DC_H:((69)) DIM_H _UREL_INTER ALL DC 0 0 0 6
DC_H:((69)) DIM_H _AREA ALL DC 0 0 0 8
DC_H:((69)) DIM_H _DST_TZ_RNC ALL DC 0 0 0 1
DC_H:((69)) DIM_H _NODEB ALL DC 0 0 0 3
DC_H:((69)) DIM_H _UREL_INTRA ALL DC 0 0 0 5
DC_H:((69)) DIM_H _GSMREL ALL DC 0 0 0 7
DC_H:((69)) DIM_H _RNCNB ALL DC 0 0 0 9

UClass
----------------------
VERSIONID CLASSNAME UEXTENSION DESCRIPTION PARENT OBJ_BH_REL ELEM_BH_REL INHERITANCE ORDERNRO
--------------------------------------------------------------------
DC_H:((69)) RNC ALL RNC Topology Topology 0 0 0 0.00E+00
DC_H:((69)) DST_TZ_NODEB ALL NODEB DST and TZ Site Topology Topology 0 0 0 2
DC_H:((69)) UCELL ALL UTRAN Cell Topology Topology 0 0 0 4
DC_H:((69)) UREL_INTER ALL UTRAN Inter Cell Relation Topology Topology 0 0 0 6
DC_H:((69)) AREA ALL Cell Area Topology Topology 0 0 0 8
DC_H:((69)) DST_TZ_RNC ALL RNC DST and TZ Site Topology Topology 0 0 0 1
DC_H:((69)) NODEB ALL NodeB Topology Topology 0 0 0 3
DC_H:((69)) UREL_INTRA ALL UTRAN Intra Cell Relation Topology Topology 0 0 0 5
DC_H:((69)) GSMREL ALL GSM Cell Relation Topology Topology 0 0 0 7
DC_H:((69)) RNCNB ALL RNCNB Topology Topology 0 0 0 9

I tried the query as you mentioned –

SELECT t1.owner,t1.tablename,t1.alias,t1.uextension,t2.parent,t2.classname,t2.description
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY versionid ORDER BY versionid) AS Seq,* FROM UniverseTable) t1
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY versionid ORDER BY versionid) AS Seq,* FROM UniverseClass) t2
ON t1.versionid = t2.versionid
AND t1.Seq = t2.Seq and
t1.versionid LIKE '%DC_H:((69))%'and t2.classname Not like '%DC_H%'

This shows only one row as follows –
-------------------------------------------------------------------
owner tablename alias uextension parent classname description
-------------------------------------------------------------------
DC DIM_H _RNC ALL Topology RNC RNC Topology

Where I want the result to be –
---------------------------------------------------------------
owner tablename alias uextension parent classname description
---------------------------------------------------------------
DC DIM_H _RNC ALL Topology RNC RNC Topology
DC DIM_H _DST_TZ_NODEB ALL Topology DST_TZ_NODEB NODEB DST and TZ Site Topology
DC DIM_H _UCELL DIM_H _UCELL_NEIGHBOUR ALL Topology UCELL UTRAN Cell Topology
DC DIM_H _UREL_INTER ALL Topology UREL_INTER UTRAN Inter Cell Relation Topology
DC DIM_H _AREA ALL Topology AREA Cell Area Topology
DC DIM_H _DST_TZ_RNC ALL Topology DST_TZ_RNC RNC DST and TZ Site Topology
DC DIM_H _NODEB ALL Topology NODEB NodeB Topology
DC DIM_H _UREL_INTRA ALL Topology UREL_INTRA UTRAN Intra Cell Relation Topology
DC DIM_H _GSMREL ALL Topology GSMREL GSM Cell Relation Topology
DC DIM_H _RNCNB ALL Topology RNCNB RNCNB Topology

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 11:04:36

i think you can link using extension and versionid

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 11:10:07

I tried using Extension and Versionid. It is still performing the Cartesian Product of both the tables.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 11:18:05

nope it wont as per sample data posted

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vrushali
Starting Member

7 Posts

Posted - 2011-08-30 : 11:31:14

Please check this -

SELECT t1.owner,t1.tablename,t1.alias,t1.universeextension,t2.parent,t2.classname,t2.description
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY versionid ORDER BY versionid) AS Seq,* FROM UniverseTable) t1
INNER JOIN (SELECT ROW_NUMBER() OVER (PARTITION BY versionid ORDER BY versionid) AS Seq,* FROM UniverseClass) t2
ON t1.versionid = t2.versionid
AND t1.universeextension = t2.universeextension and
t1.Seq = t2.Seq

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-30 : 12:25:55

[code]
SELECT *
FROM UniverseTable t1
JOIN UniverseClass t2
ON t1.versionid = t2.versionid
AND REPLACE(t1.UEXTENSION,'_','') = t2.CLASSNAME
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources