Yet another Join Query Question

[soklear]

Howdy.

#1 - I have three tables at this point and will have up to 8 more that I will need to 'join' together. Each table has/will have 2 columns, the first is Period Column, the second is my calculated column.

So, tables look like this:

Table_1 Table_2
HR Cls HR Blah
-- --- -- ---
0 - 1 8 0 - 1 1
1 - 2 3 1 - 2 2
2 - 3 4 2 - 3 3

Table_3
HR Gumby
--- -----
0 - 1 10
1 - 2 320
2 - 3 14

So, I have a join statement that looks like this for two of the tables:


SELECT Table_1.HR as 'Period',
Table_2.cls as 'Total Calls',
Table_3.blah as 'Cls'

FROM Table_1 JOIN Table_2 ON Table_1.HR = Table_2.HR

and returned is:

HR Cls Blah
---- ---- ------
0-1 8 1
1-2 3 2
2-3 4 3

But, when I incorporate the third table using the below right join:

Select *
From Table_1 a right join Table_2 b
on (a.hr = b.hr)
right join Table c
on (b.hr = c.hr) ;

The result from the above query is all the rows are returned in one table, which is not what I want.

I want the below result:

HR Calls Blah Gumby
---- ------ ------ ---------
0-1 8 1 10
1-2 3 2 320
2-3 4 3 14

How do I incorporate the third table (and all others consequently)? I really have tried to answer this for myself, but, am stuck. Any help on this is MUCH appreciated.

Thanks!

Bob

 

[Walid]

Select *
From Table_1 a right join Table_2 b
on (a.hr = b.hr)
right join Table c
on (b.hr = c.hr) ;

The result from the above query is all the rows are returned in one table, which is not what I want.

That is not what you want, but it is what you asked for.
SELECT * FROM TableName1 will return all the rows (there is no WHERE filter) no matter what joins you used in the statement. You asked to return all the rows from one table.

Use this one instead

SELECT Table_1.HR as 'Period',
Table_1.cls as 'Total Calls',
Table_2.blah as 'BlahBlah',
Table_3. Gumby as ' Gumby '
FROM Table_1
JOIN Table_2 ON Table_1.HR = Table_2.HR
JOIN Table_3 ON Table_1.HR = Table_3.HR



Walid Magd
Engwam@Hotmail.com

 

[Pattycake245]

soklear,

Tested this and it works:

You need to specify specific columns, select * will return every field from every table you are including

Select a.hr, a.cls, b.blah, c.gumby
From Table_1 a right join Table_2 b
on (a.hr = b.hr)
right join Table_3 c
on (b.hr = c.hr)

Tim

 

[soklear]

Walid, YOU ROCK!!!! For some reason I thought using the select * would work... probably from ready the SQL for Dummies book, which has answered most of my questions... anyways-

Thanks I really do appreciate the help.

Bob

 

[MDXer]

* Does work in the manner it was meant to work, it just doesn't produce the desired results. I hate books that teach people to use *.

"Shoot Me! Shoot Me NOW!!!"
- Daffy Duck