Joining 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 tables

Author

Topic

cwiedemann
Starting Member

6 Posts

Posted - 2008-02-10 : 12:25:54

Hi,

I'm having difficulties joining two tables. I have

Table 1:
--------
T1_1 T1_2 T1_3
10 100 a
10 150 a
20 50 a
30 300 b
30 120 a
40 560 a
40 230 b

Table 2:
--------
T2_1 T2_2 T3_3
10 1 3
10 1 4
20 1 2
20 1 8
30 0 4
40 1 5
40 1 7

Now I want to join these two tables (primary key TX_1) and get the sum of column T1_2 under the condition that T1_3 = a and T2_2 = 1 so that I get

Resulting table: T1_3 = a AND T2_2 = 1
----------------
T1_1 SUM(T1_2)
10 250
20 50
40 560

but I am at a complete loss as how to accomplish this! Any help would be highly appreciated. I hope I have provided enough information to make my question comprehensible.

Cheers,
Christin

singularity
Posting Yak Master

153 Posts

Posted - 2008-02-10 : 12:49:43

SELECT T1_1, SUM(T1_2)
FROM Table1
JOIN Table2 ON Table1.T1_1 = Table2.T2_1
WHERE T1_3 = 'a' AND T2_2 = 1
GROUP BY T1_1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 12:51:06

Use this:-

SELECT t1.T1_1,SUM(DISTINCT T1_2)
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.T2_1=t1.T1_1
AND T2_2=1
AND T1_3='a'
GROUP BY t1.T1_1

cwiedemann
Starting Member

6 Posts

Posted - 2008-02-10 : 13:11:15

quote:
Originally posted by singularity

SELECT T1_1, SUM(T1_2)
FROM Table1
JOIN Table2 ON Table1.T1_1 = Table2.T2_1
WHERE T1_3 = 'a' AND T2_2 = 1
GROUP BY T1_1

Hmmm...no, this sums all entries, not only distinct ones.

cwiedemann
Starting Member

6 Posts

Posted - 2008-02-10 : 13:13:02

quote:

Originally posted by visakh16

Use this:-

SELECT t1.T1_1,SUM(DISTINCT T1_2)
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.T2_1=t1.T1_1
AND T2_2=1
AND T1_3='a'
GROUP BY t1.T1_1

This doesn't seem to work either, the reply I get is "Table1.T1_1 is invalid...not contained...". Maybe I'm doing it wrongly.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 13:15:44

quote:
Originally posted by cwiedemann

quote:
Originally posted by visakh16

Use this:-
SELECT t1.T1_1,SUM(DISTINCT T1_2)
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.T2_1=t1.T1_1
AND T2_2=1
AND T1_3='a'
GROUP BY t1.T1_1

This doesn't seem to work either, the reply I get is "Table1.T1_1 is invalid...not contained...". Maybe I'm doing it wrongly.

did you use the same query including aliases? else post the query used by you.

cwiedemann
Starting Member

6 Posts

Posted - 2008-02-10 : 13:52:27

quote:
Originally posted by visakh16

quote:
Originally posted by cwiedemann

quote:
Originally posted by visakh16

Use this:-
SELECT t1.T1_1,SUM(DISTINCT T1_2)
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.T2_1=t1.T1_1
AND T2_2=1
AND T1_3='a'
GROUP BY t1.T1_1

This doesn't seem to work either, the reply I get is "Table1.T1_1 is invalid...not contained...". Maybe I'm doing it wrongly.

did you use the same query including aliases? else post the query used by you.

I've got Table1 with field T1_1 and normally I would write something like

SELECT Table1.T1_1, Table2.T2_1
FROM Table1
INNER JOIN Table2
ON Table1.T1_1 = Table2.T2_1

to display T1_1 (and it would give me multiple hits for each value of T1_1). In reality "Table1" is a different name, but the structure is just the same. Hope this helps :)

cwiedemann
Starting Member

6 Posts

Posted - 2008-02-10 : 15:18:58

Ok, so now I'm doing something somewhat different:

SELECT TOP 5 Table1.T1 AS T1,
Table1.T2 AS T2,
Table1.T3 AS T3,
SUM(Table1.T4) AS SumT4
FROM Table1
WHERE Table1.T5 = 'abc'
AND Table1.T6 = 123
GROUP BY Table1.T1,
Table1.T2,
Table1.T3
ORDER BY SumT4

Now I want to add another condition based on Table2, i.e. I want the entries that are picked out to also fulfill

Table2.T7 = 1

The problem is that in Table2 there are multiple entries for the primary key T1, but they all have the same T7 value, i.e. Table 2 might look like

T1 ... T7...
10 1
10 1
10 1
20 1
30 0
30 0

How can I do this?

Cheers,
Christin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 22:44:13

You seem to change your requirement with every post. The query provided now has more columns than your original post. Can you please post what exactly you are looking at and also provide structure & sample data of tables with expected o/p?

cwiedemann
Starting Member

6 Posts

Posted - 2008-02-11 : 02:33:07

quote:
Originally posted by visakh16

You seem to change your requirement with every post. The query provided now has more columns than your original post. Can you please post what exactly you are looking at and also provide structure & sample data of tables with expected o/p?

Yes, I am sorry - I am trying different things hoping to arrive at something that works. Here's an example:

Table1.T1 Table1.T2 Table1.T3 Table1.T4
10 2 120 a
10 2 140 a
10 2 160 b
20 3 80 a
20 3 100 a
30 4 90 a
30 4 105 a

Table2.T1 Table2.T2 Table3.T3
10 2 1
10 2 1
20 3 0
20 3 0
30 4 1
30 4 1

Now what I want is the sum Table1.T3 for all enteries that have Table1.T4 = 'a' and Table2.T3 = '1'. Furthermore, I want to make a cut on the sum, e.g. SUM(Table1.T4)>100 AND SUM(Table1.T4)<200.

Then the result I want from the example tables above is

T1 T2 SumT4
30 4 195

I thinkk I got it right this time, and that this is what I want and all that I want...

Cheers,
Christin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 03:42:04

Ok. here it is:-

--create reqd. tables
CREATE TABLE Table1
(
T1 int,
T2 int,
T3 int,
T4 char(1)
)

CREATE TABLE Table2
(
T1 int,
T2 int,
T3 int
)

--populate data
INSERT INTO Table1 VALUES(10, 2, 120, 'a')
INSERT INTO Table1 VALUES(10, 2, 140, 'a')
INSERT INTO Table1 VALUES(10, 2, 160, 'b')
INSERT INTO Table1 VALUES(20 ,3, 80, 'a')
INSERT INTO Table1 VALUES(20 ,3, 100, 'a')
INSERT INTO Table1 VALUES(30 ,4, 90, 'a')
INSERT INTO Table1 VALUES(30 ,4 ,105, 'a')



INSERT INTO Table2 VALUES(10, 2, 1)
INSERT INTO Table2 VALUES(10, 2, 1)
INSERT INTO Table2 VALUES(20, 3, 0)
INSERT INTO Table2 VALUES(20, 3, 0)
INSERT INTO Table2 VALUES(30, 4, 1)
INSERT INTO Table2 VALUES(30, 4, 1)

--soln.
SELECT t1.T1,t1.T2,SUM(DISTINCT t1.T3) AS SumT4
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.T1=t1.T1
AND t1.T4='a'
AND t2.T3=1
GROUP BY t1.T1,t1.T2
HAVING SUM(DISTINCT t1.T3) >100
AND SUM(DISTINCT t1.T3) <200



--output
T1          T2          SumT4
----------- ----------- -----------
30          4           195

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources