August 14, 2008 at 4:55 am
Hi All,
I am trying to join data from the same database for a particular student from the 6 tables below, using a unique column: (individual_id = 10734589)
Tables
--------
recent_student
recent_student_graduate_2330
recent_student_professional
recent_individual
recent_student_graduate
recent_student_organisation
there is data for other students in the tables but I only need that of the student with the individual_id above, am thinking of using a join statement, does anyone have any sql query I could use.
This query is to be used to copy the result into another set of similar tables on the same database using an SSIS package, any suggestions would be highly appreciated.
Thanks
August 14, 2008 at 5:37 am
Hi,
If I've understood correctly:
SELECT
* --replace with what you want if not all
FROM
recent_student RS
JOIN
recent_student_graduate_2330 SG2 ON RS.individual_id = SG2.individual_id
JOIN
recent_student_professional SP ON RS.individual_id = SP.individual_id
JOIN
recent_individual RI ON RS.individual_id = RI.individual_id
JOIN
recent_student_graduate RSG ON RS.individual_id = RSG.individual_id
JOIN
recent_student_organisation RSO ON RS.individual_id = RSG.individual_id
WHERE
RS.individual_id = 10734589
August 14, 2008 at 5:44 am
There's not enough information to answer this question fully. Yes, you need to use JOIN's to put the tables together, but what columns constitute the primary and foreign keys that link the tables up? Without those, it's not possible to suggest a query to you. If you do have those though, the query would look something like this:
SELECT...
FROM TableA a
JOIN TableB b
ON a.PK = b.FK
JOIN TAbleC c
ON b.PK = c.FK
The names of the columns that make a foreign key may be the same as the names of the primary key column in the table you're linking to.
This sounds like homework. You might want to study & read the book a bit more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2008 at 6:02 am
Thanks for your reply Grant, there are actually no primary and foreign keys on the tables.
August 14, 2008 at 6:07 am
Well, to quote Adam from Myth Busters, "That's your problem, right there."
Relational data storage more or less requires relationships and those are defined through constraints on the tables. Probably, even without actual PK's and FK's, you've got columns that would function as such. Use those, as was suggested in the 2nd post above.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2008 at 8:06 am
Thanks for the contributions, I tried the tsql sent by adam below:
SELECT
* --replace with what you want if not all
FROM
recent_student RS
JOIN
recent_student_graduate_2330 SG2 ON RS.individual_id = SG2.individual_id
JOIN
recent_student_professional SP ON RS.individual_id = SP.individual_id
JOIN
recent_individual RI ON RS.individual_id = RI.individual_id
JOIN
recent_student_graduate RSG ON RS.individual_id = RSG.individual_id
JOIN
recent_student_organisation RSO ON RS.individual_id = RSG.individual_id
WHERE
RS.individual_id = 10734589
However, no rows are returned in the results, all empty, I did a select * on all the 6 tables and found out that the table "recent_student_professional" contains no data, could this be the reason why am getting no results.
August 14, 2008 at 8:15 am
So change the JOIN on that table to a LEFT JOIN. That means all the data will return from the main table and only those rows that match from the second table based on the filter criteria.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2008 at 8:16 am
Hi Fosco,
That is highly likely the reason. You can use LEFT JOIN to select all records from the first table (on the left) and any matched records from the second table (table on the right). It's well worth looking in BOL.
As Grant said, my answer is based on assumptions ... but following along those lines, this should resolve....
SELECT
* --replace with what you want if not all
FROM
recent_student RS
JOIN
recent_student_graduate_2330 SG2 ON RS.individual_id = SG2.individual_id
JOIN
recent_individual RI ON RS.individual_id = RI.individual_id
JOIN
recent_student_graduate RSG ON RS.individual_id = RSG.individual_id
JOIN
recent_student_organisation RSO ON RS.individual_id = RSG.individual_id
LEFT JOIN --This is the change I've made and have moved it to the bottom to try and help it make sense.
recent_student_professional SP ON RS.individual_id = SP.individual_id
WHERE
RS.individual_id = 10734589
EDIT: Grant beat me to it.
August 14, 2008 at 10:04 am
Thanks to you Adam and Grant, I got a result now, Its quite a long row, I noticed that the column 'RS.individual_id = 10734589 ' was repeated a few times, followed by other columns in the same row, is that supposed to happen, please excuse my ignorance guys.
August 14, 2008 at 10:11 am
No problem.
Yes, that is correct. Basically, specifying "SELECT *" is saying return all columns from all of the tables in the query. You can replace the * with either table specifics (such as RS.*) or you can put specific column names against table aliases (such as RS.individual_id). As an example, if you wanted to return all the columns from recent_student and a column named Grade from recent_student_graduate, you could use the following...
SELECT
RS.*,
RSG.Grade
FROM
recent_student RS
JOIN
recent_student.....
....etc.
August 14, 2008 at 10:21 am
Thanks, you've been very helpful, much appreciated, will have a look at BOL for further info.
My next task is to create an SSIS package for copying the result I just got to another set of 6 similar tables on the same database, do I use import/export wizard or BIDS in visual studio, any suggestions please.
August 14, 2008 at 10:33 am
Personally, I'd use BIDS, but that has a bit of a learning curve to it. You might want to start with the wizard to see if it will do what you need.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 14, 2008 at 12:29 pm
Thanks, much appreciated.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply