Selecting from multiple tables and displaying in new table

[ontim]

Hi there,

I am doing a project for "Students Management System" using SQL server 7. I have all the rquired data of the students within 5 different tables but in a single database.I am trying to display all the data from 5 different tables into 1 single or in a new table.
Now my problem is that I am not able to figure it out, how to do that.There is one column called 'Rollno' common in all the tables.So I have also tried to do with alias joins,but in doing so it reduces the amount of results and also gives duplicate records.Since there are around 1500 records and a lot of rows have null value,how could I eliminate those blank records.. So please help me out,how can I display the combined data into a new single table.Also I would like to know how to export the query results into text files or convert it into HTML pages.Is there any free tool available for that online.
Detail explanation would be helpful and appreciated.

Many thanks in advance.

 

[esdee]

as for the query - the way i see it you should have problems retrieving your data or else you'll probably have to redesign the db: normalize it, set defaults whenever needed, if you want to avoid nulls.
as for the export:
ms sql server 2000 has the 'for xml' clause which exports to xml in given format, but i see you have sql server 7, so there you could probably use the bcp utility or dts

 

[ontim]

Hi there,

Stoyanbd thanks for your advice,this would help me to eliminate null records,but I still have no idea about the first part as to how can I display all the records from 5 tables into one single table avoiding duplicates.For detailed description see my message in the post.

http://www.tek-tips.com/gthreadminder.cfm/lev2/4/lev3/27/pid/183

A still more detailed explanation would be helpful.

Thanks once again

 

[esdee]

the way i see it you should NOT have problems retrieving data with several joins from those 5 or so tables.
may be if you shared the database layout I or someone else could write a sample query to help?

greets

 

[aalmeida]

Have you tryed to use a UNION?
AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[ontim]

Hi there,
Sorry for delay in replying.Thanks stoyanbd and aalmeida
I really appreciate your quick response and help.But I still need more details for query retrieving.
Aalmeida,I would definately try using Union but that doesn't solve my first part i.e selecting from multiple tables.

Stoyanbd,regarding your message,the details which you required are as under:-
There are total 7 tables :- students table has PK as rollno column.
degree table :- which has PK as edu_code and rollno as simple column.
information table:- which has PK as rollno column.
qualification table :- which has PK as qual_code and rollno as simple column.
class table :- which has PK as class_code and rollno as simple column.
details table:- which has PK as rollno column.
login table:- which has PK as roll column which corresponds to rollno column in students table.
So in all out of 7 tables 3 tables have Primary key as 'rollno' and 1 table has 'roll' column as PK which corresponds to 'rollno' column in students column.
Please note that 'rollno' column is present in all tables except the last table which has 'roll' column instead of 'rollno' as PK but both correspond to same details.

I hope this would help you better in helping me with detailed explanation.

Thanks once again,
tim.

 

[esdee]

i guees you should try smth like

select * from students s
join degree d on s.rollno = d.rollno
join info i on s.rollno = i.rollno

etc etc

 

[ontim]

Hi there,

Stoyanbd, I tried using 'Joins' as you have mentioned but in doing so it gives duplicate records in all the columns with the original records because the number of records in all the tables are not the same.In students it is 1500.In degree the records are 1200 since 300 students don't have full degress and so they are null.Similarly in
information table there are more records since their information is more and so on.
Please help me in solving my problem.

Thanks once again
tim

 

[aalmeida]

I think you missunderstanding the UNION deal, it does help you with all your requirements, just read this from BOL:
"Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. This is different from using joins that combine columns from two tables.

Two basic rules for combining the result sets of two queries with UNION are:

The number and the order of the columns must be identical in all queries.


The data types must be compatible.
"

Now try something like this:
SELECT Col1, Col2 FROM students
UNION
SELECT Col3, Col4 FROM degree
UNION
SELECT Col5, Col6 FROM info
ORDER BY Col1, Col2
I know this works because I'm using the same at some of my applications




AL Almeida
NT/DB Admin
"May all those that come behind us, find us faithfull"

 

[ontim]

Hi there,
Aalmeida thanks for this one.We are a little close.I have tried as you said,but in doing so it gives me a error which says :- "All queries in an SQL statement containing a UNION operator must have an equal number of expressions in their target lists." But when I changed the 'order by' columns and tried then the query results were all jumbled up i.e one column showing other column's records & vice-versa and only those 2 columns which were mentioned after 'ORDER BY' were shown.
Please bear with me as I am giving the detailed schema which may be useful.

Table 1:-Student Table 2:- Degree
rollno(PK)(varchar) rollno(FK)(varchar)
fname (&quot deg_code(PK)(varchar)
lname (varchar) deg_yr (char)
add (&quot deg_fld (varchar)
state (&quot deg_sp (varchar)
etc. etc.
---------------------------------------------------
Table 3:- Information Table 4:- Qualification
rollno(PK)(varchar) rollno(FK)(varchar)
add_qual(text) qual_code(PK)(&quot
qual_yr (varchar) qual_yr (char)
lang_sp (&quot qual_course(varchar)
lang_write (&quot etc.
etc.
----------------------------------------------------------
Table 5:- Tests Table 6:- Details Table 7:- Login
rollno(FK)(varchar) rollno(PK)(varchar) roll(PK)(varchar)
test_code(PK)(&quot remarks (&quot LoginID (&quot
test_cat (varchar) complaints (&quot Password (&quot
test_subcat (text) etc. etc.
etc.
------------------------------------------------------------
Pls. note that 'roll' column in 'login' table corresponds to the 'rollno' column in'student' table and both have same details.
All the tables have different numbers of columns and different number of records.
Now taking 'rollno' as common I would like to display my records as :-

rollno|fname|lname|add|deg_yr|deg_fld|deg_sp|add_qual|qual_yr|lang_sp|qual_yr|qual_course|test_cat|test_subcat|remarks
|complaints|LoginID|Password

Now suppose if there are 2 deg_fld or 2 add_qual then only those records should be shown twice and not all the records corresponding to it from another table should be displayed.
Please explain me in detail,how can I do so.

Many many thanks once again
tim

 

[GolfBoy]

I have looked at your table design and to me all the rollno columns should be foreign keys to that of the student table rollno Primary Key.
Anyway in your query you need to use the Left Outer Join(=*) or the Right Outer Join (*=) when joining your tables together.

E.G.1
SELECT S.RollNo,S.fname,S.lname,D.deg_yr,D.deg_fld
FROM Student S
LEFT OUTER JOIN Degree D ON S.RollNo = D.RollNo
*** This will return all the records from the student table and any records that match in the degree table, you will get null values from the degree table if no match was found for a student record.

E.G.2
SELECT S.RollNo,S.fname,S.lname,D.deg_yr,D.deg_fld
FROM Student S
RIGHT OUTER JOIN Degree D ON S.RollNo = D.RollNo
*** This will return all the degree records and any student records that match, you will get null values from the student table if no match was found for a degree record.

E.G.3
SELECT S.RollNo,S.fname,S.lname,D.deg_yr,D.deg_fld
FROM Student S
INNER JOIN Degree D ON S.RollNo = D.RollNo
*** This will only return the student and degree records that match.

Hope this is what you needed.

Swing Tight and True:- The Tiger………

 

[ontim]

Hi there,

Thanks Golfboy, for the solution you have given to me.I think this should work properly for my schema.Since I am trying it fully now, I have not finished yet.So if I have some problem or if there is still some problem then I surely will ask you. Meanwhile let me try the solution that you have given to me.I hope this should work.
Thanks once again
tim

 

[ontim]

Hi there,

Thanks Golfboy,I have tried using your tip and it works fine only till 2 tables but when I try to join 7 tables as shown in the schema in my previous mail ,it does not give me exact result.Since I feel using LEFT OUTER JOIN is more relevant it reduces the records when I join all the tables and also duplicates the records for all columns and not as what I have mentioned below the schema in my previous post.
If you need any more details please let me know but please help me in solving this problem.

Thanks once again
tim