Inner Join a colum from 3 tables!

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

Inner Join a colum from 3 tables!

Author

Topic

louiedogg418
Starting Member

28 Posts

Posted - 2011-03-05 : 10:52:52

so I just started my database concepts class and this is a question thats due a couple weeks down the road:

You have three tables. Their names are TableA, TableB, and TableC. Each of the tables contain a key column called 'RecordId". Please code the sql to create a view that returns all columns in all tables with an inner join on column RecordId.

And this is what i got:

SELECT TableA.RecordId AS TableA_RecordId, TableB.RecordId AS TableB_RecordId, TableC.RecordId AS TableC_RecordId
FROM (TableC INNER JOIN TableB ON TableC.[RecordId] = TableB.[RecordId]) INNER JOIN TableA ON TableB.[RecordId] = TableA.[RecordId];

Is this even close?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-05 : 11:24:15

are you getting any error while executing the above query ? for me it seems to be ok !

Cheers
MIK

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-03-05 : 13:53:41

Even for a dummy example, I hate the data element name “record_id” on principle. Rows are not records, columns are not fields and tables are not files. Learn the right words and EXACTLY what they mean from the start, and you will write good code without even thinking about it.

You are displaying redundant data – we hate redundancy in RDBMS. There is no need for those silly proprietary square brackets. The infixed INNER JOIN operator is a sign of Noob. In short, you over-did it. All you need is:

SELECT TableA.something_id
FROM TableA, TableB, TableC
WHERE TableC.something_id = TableB.something_id
AND TableB.something_id = TableA.something_id;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

louiedogg418
Starting Member

28 Posts

Posted - 2011-03-06 : 00:00:41

Yes i am a noob, like i said its for a database concepts class... ok so i am fairly fluent in html and psuedo code, but explain to me how the equal signs work in sql... I get this much

SELECT TableA.something_id
FROM TableA, TableB, TableC
WHERE TableC.something_id // but then how does it = TableB.something_id
AND TableB.something_id = TableA.something_id;

all help is appreciated!!

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-03-06 : 02:53:47

quote:
Originally posted by louiedogg418

Yes i am a noob,

Don't worry about it.

quote:
Originally posted by louiedogg418
SELECT TableA.something_id
FROM TableA, TableB, TableC
WHERE TableC.something_id // but then how does it = TableB.something_id
AND TableB.something_id = TableA.something_id;

In the example query given above, three tables are being joined.

The query must be told how to assemble the tables together, it must know how to match rows in TableA to TableB and to TableC.

The portion:

WHERE TableC.something_id = TableB.something_id
AND TableB.something_id = TableA.something_id;

tells the query how to do that.

"something_id" is a common column between the three tables. It is assumed here that there is a common column between all three tables. If there is no common column, it will be somewhat difficult to join the tables together.

This syntax:

FROM TableA, TableB, TableC
WHERE TableC.something_id = TableB.something_id
AND TableB.something_id = TableA.something_id;

has been replaced by:


    FROM TableA
         INNER JOIN
         TableB
         ON TableA.something_id = TableB.something_id
         INNER JOIN
         TableC
         ON TableB.something_id = TableC.something_id

Where the type of JOIN and what columns are used are explicitly declared in the FROM clause. With explicit declaration, you can shift to LEFT, RIGHT, and FULL OUTER joins directly if necessary.

JOIN Fundamentals: [url]http://msdn.microsoft.com/en-us/library/ms191517.aspx[/url]

Using Joins: [url]http://msdn.microsoft.com/en-us/library/ms191472.aspx[/url]

Difference Between Join in WHERE and FROM: [url]http://msdn.microsoft.com/en-us/library/aa213235%28v=SQL.80%29.aspx[/url]

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-06 : 05:54:01

quote:
Each of the tables contain a key column called 'RecordId". Please code the sql to create a view that returns all columns in all tables with an inner join on column RecordId.

In addition to what was already answered you have to list all columns from all tables in select list, not just recordId. Because recordId value is the same for matched rows, it is enough to list just recordId from TableA and then to list all other columns from tableA, tableB and tableC.

I don't want to write exact query, because it is your homework, there is no point doing it for you. Write a complete statement that you think is the solution then post it here, so we can tell you if it is correct or give you further clue.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

louiedogg418
Starting Member

28 Posts

Posted - 2011-03-06 : 10:02:10

ok so this is what I have come up with...

Select tableA.recordId
From TableA INNER JOIN
TableB
ON TableA.RecordId=TableB.RecordId
inner join
tableC
on TableB.recordId=TableC.recordid
Cross Join TableA,TableB,TableC

Is that how all the colums n rows wouldcoma back WITH the recordID inner joined? and in the inner join syntax does it matter the order i.e. Start with the tableA and equal it to table B, n then inner join tableB to tableC? does it have to be alphabetically or is it just to keep it organized and clean?

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2011-03-06 : 13:23:02

You don't need cross join at the end of query, it does not make any sense. With the rest of query you basically asked to get all tableA.recordId values from relation ("complex row") consisting of all attributes from three tables with the same recordId. Think of it as if you did concatenation of rows with the same recordId.

However, you did not want to return just recordId from each row, but also all other columns. So in you select clause, you need to list column names from each table involved.

Mirko

My blog: http://mirko-marovic-eng.blogspot.com/

louiedogg418
Starting Member

28 Posts

Posted - 2011-03-06 : 17:51:22

ahh ok, thats the thing, the only info the question gave was the record.Id column, didnt not name anything else but the record.id, and the tables... so im guessing take of the cross join and it should be correct right?

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-03-06 : 19:15:44

quote:
Originally posted by louiedogg418

Please code the sql to create a view that returns all columns in all tables with an inner join on column RecordId.

quote:
Originally posted by louiedogg418

Is that how all the colums n rows wouldcoma back WITH the recordID inner joined?

These are different requirements.

Your Original Requirement: Show all columns using INNER JOIN. If RecordID was the only column in each table, then your original query was already succeeding. If RecordID was not the only column in each table, you would have needed to have listed more column names in the SELECT clause.

Your Second Requirement: Show all columns and rows. No JOIN requirement specified, but I will assume INNER JOIN is still required. Without seeing your data, it is hard to know what query will show all rows. The technical assumptions would be, RecordID was the primary key of TableA, and was a foreign key to TableB and TableC. When you are forced to use INNER JOIN, then only way all rows will be retrieved is if both TableB and TableC happen to have exactly the same number of rows as TableA and those rows would also both have exactly the same key values, like:

TableA
RecordID
1
2

TableB
RecordID
1
2

TableC
RecordID
1
2

Note that I had to make at least two assumptions about what is happening.

Are you sure you have not been given a trick question? Because the "all rows" requirement typically requires an OUTER JOIN (LEFT, RIGHT, or FULL), not an INNER JOIN.

I remember my high school chemistry teacher telling me that in his college inorganic chemistry class, his teacher gave everyone a material sample and asked them to identify what it was. He performed every test he could think of (based on the class) on the sample and he could not figure it out. He turned in his assignment with, "I have no idea." It was the right answer because the teacher had given him ground up Cornflakes, an organic material. Apparently the teacher gave one student in every class a trick question just to see if a desperate student would come up with some ridiculous answer.

louiedogg418
Starting Member

28 Posts

Posted - 2011-03-06 : 21:21:35

Sounds like your chem teacher was a fun guy... and yea, the original question was stated like this, with no data at all, and no tables at all, the following is as it was exactly written down.

Bonus...You have three tables. Their names are TableA, TableB, and TableC. Each of the tables contain a key column called 'RecordId". Please code the sql to create a view that returns all columns in all tables with an inner join on column RecordId.

So maybe you are right about the trick question part.... But I am just going with what you have all helped me come up with. I will let you know what happens :)

chris_n_osborne
Starting Member

34 Posts

Posted - 2011-03-06 : 22:24:31

quote:
Originally posted by louiedogg418

Bonus...You have three tables. Their names are TableA, TableB, and TableC. Each of the tables contain a key column called 'RecordId". Please code the sql to create a view that returns all columns in all tables with an inner join on column RecordId.

There is no requirement for all rows in this question. Just for all columns in a VIEW.


CREATE TABLE TableA
(RecordID          INTEGER
,TableAData        INTEGER NOT NULL
,CONSTRAINT pk_TableOne PRIMARY KEY (RecordID)
)

CREATE TABLE TableB
(TableBID          INTEGER
,RecordID          INTEGER
,TableBData        INTEGER NOT NULL
,CONSTRAINT pk_TableB PRIMARY KEY (TableBID)
,CONSTRAINT fk_TableB_TableA_RecordID
            FOREIGN KEY (RecordID)
            REFERENCES TableA (RecordID)
)

CREATE TABLE TableC
(TableCID          INTEGER
,RecordID          INTEGER
,TableCData        INTEGER NOT NULL
,CONSTRAINT pk_TableC PRIMARY KEY (TableCID)
,CONSTRAINT fk_TableC_TableA_RecordID
            FOREIGN KEY (RecordID)
            REFERENCES TableA (RecordID)
)

INSERT INTO TableA
SELECT 1, 5 UNION ALL
SELECT 2, 6 UNION ALL
SELECT 3, 7

INSERT INTO TableB
SELECT 1, 1, 10 UNION ALL
SELECT 2, 1, 20 UNION ALL
SELECT 3, 2, 30 UNION ALL
SELECT 4, 2, 40

INSERT INTO TableC
SELECT 1, 1, 50 UNION ALL
SELECT 2, 1, 60 UNION ALL
SELECT 3, 2, 70 UNION ALL
SELECT 4, 2, 80
go

--SELECT * FROM TableA
--SELECT * FROM TableB
--SELECT * FROM TableC

CREATE VIEW AllColumns
AS
  SELECT T1.RecordID
        ,T1.TableAData
        ,T2.TableBID
        ,T2.RecordID AS RecordID_FK_FromTableB
        ,T2.TableBData
        ,T3.TableCID
        ,T3.RecordID AS RecordID_FK_FromTableC
        ,T3.TableCData
    FROM TableA AS T1
         INNER JOIN
         TableB AS T2
         ON T1.RecordID = T2.RecordID
         INNER JOIN
         TableC AS T3
         ON T1.RecordID = T3.RecordID
go

SELECT A1.* -- You will want to list all column names here.
  FROM AllColumns AS A1
go

DROP VIEW AllColumns
DROP TABLE TableC
DROP TABLE TableB
DROP TABLE TableA
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources