SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Inner Join a colum from 3 tables!
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

louiedogg418
Starting Member

28 Posts

Posted - 03/05/2011 :  10:52:52  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 03/05/2011 :  11:24:15  Show Profile  Reply with Quote
are you getting any error while executing the above query ? for me it seems to be ok !

Cheers
MIK

Edited by - MIK_2008 on 03/05/2011 11:25:53
Go to Top of Page

jcelko
Esteemed SQL Purist

USA
547 Posts

Posted - 03/05/2011 :  13:53:41  Show Profile  Visit jcelko's Homepage  Reply with Quote
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
Go to Top of Page

louiedogg418
Starting Member

28 Posts

Posted - 03/06/2011 :  00:00:41  Show Profile  Reply with Quote
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!!
Go to Top of Page

chris_n_osborne
Starting Member

USA
34 Posts

Posted - 03/06/2011 :  02:53:47  Show Profile  Reply with Quote
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: http://msdn.microsoft.com/en-us/library/ms191517.aspx

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

Difference Between Join in WHERE and FROM: http://msdn.microsoft.com/en-us/library/aa213235%28v=SQL.80%29.aspx
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 03/06/2011 :  05:54:01  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

louiedogg418
Starting Member

28 Posts

Posted - 03/06/2011 :  10:02:10  Show Profile  Reply with Quote
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?
Go to Top of Page

mmarovic
Aged Yak Warrior

Czech Republic
518 Posts

Posted - 03/06/2011 :  13:23:02  Show Profile  Visit mmarovic's Homepage  Send mmarovic a Yahoo! Message  Reply with Quote
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/
Go to Top of Page

louiedogg418
Starting Member

28 Posts

Posted - 03/06/2011 :  17:51:22  Show Profile  Reply with Quote
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?
Go to Top of Page

chris_n_osborne
Starting Member

USA
34 Posts

Posted - 03/06/2011 :  19:15:44  Show Profile  Reply with Quote
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.

Edited by - chris_n_osborne on 03/06/2011 19:16:32
Go to Top of Page

louiedogg418
Starting Member

28 Posts

Posted - 03/06/2011 :  21:21:35  Show Profile  Reply with Quote
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 :)
Go to Top of Page

chris_n_osborne
Starting Member

USA
34 Posts

Posted - 03/06/2011 :  22:24:31  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000