473,396 Members | 2,158 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

Insert Into using Muliple Select

3
Hello ,

I have one question that am struggle with and I need help please.
I have two table students and courses which is each student take more than one course .
for example St1 take 2 courses (C1 , C2).
St2 take 3 courses (C1,C2, C3).

I need to create a table that contain student information plus all the courses and the score for each course in one row.

for example St1_Id ,C1_code ,C1_name ,C1_Score ,C2_code C2_name , C2_Score ..etc.

My Struggle in the insert statement
I tried the following but it show an error

Insert Into Newtable
( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score)

Select
(Select St_ID from Student)
,
(Select C_code,c_name,c_Score
from course ,student
where course.Stid =Studet.stid and C_code= 'CMP')
,
(Select C_code,c_name,c_Score
from course ,student
where course.Stid =Studet.stid and C_code= 'SYS')
;

May you please help in my inster into stmt
thank you.
Jul 17 '14 #1
5 1418
Rabbit
12,516 Expert Mod 8TB
You shouldn't create a table like this. It breaks the rules of normalization and will make it very difficult to query later on. For more on normalization, you can read our article here: http://bytes.com/topic/access/insigh...ble-structures

If you are doing this just for a report, then it is genrerally a bad idea to create a table just for that purpose. Instead, create a view that will denormalize your data. To get it in that format, you can use the PIVOT BY functionality. You can read more about pivot from the Microsoft documentation here: http://technet.microsoft.com/en-us/l...=sql.105).aspx
Jul 17 '14 #2
Sara1
3
@Rabbit
I know I should do not do this , but I need it in one record so I can apply data mining technique later on it .
Jul 17 '14 #3
Rabbit
12,516 Expert Mod 8TB
That's fine, you can create a view using the method I mentioned in my post.
Jul 17 '14 #4
Sara1
3
@Rabbit
Thank you very much for your help .
Although I tried the Pivote bY and seems I can't partition using more than one column
Jul 18 '14 #5
Rabbit
12,516 Expert Mod 8TB
No, you can't. You would have to create 2 pivots and join them.
Jul 18 '14 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Frank Natoli | last post by:
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise, of "abc". Is there any simple way to inject all the rows of "abc" into "xyz"? Tried "insert into xyz select * from abc"...
10
by: serge | last post by:
Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I...
2
by: Alexander Prozor | last post by:
Hello, does anybody using system 'Select Users, Computers or Groups' dialog box to searching in LDAP ? I use my own dialog for this purpose but using the system one is much better for me.... ...
3
by: mahajanvit | last post by:
Hi one and all I got this problem during my project. So in order to solve this I made a very small application. I am trying to insert using SP and sqldatasource control. I know that while using...
6
by: MrHelpMe | last post by:
Hello experts, O.k I give up on this. I have an LDAP query from asp working correctly. It is returning data fine. Now I need to know how to take this data and then submitt it into the...
1
by: intscript | last post by:
Hi everyone Is is possible to retrieve data using multiple select. Here is my SQL code: select sum(nPassengers) Planned, Actual, (sum(Planned) - Actual) Variance from ( select iDeptCode,...
1
by: bobble | last post by:
Is there any way to insert a record as the first record in an existing table, insert using RRN =1? What I'm building are several files to be used by Excel from an iSeries using DB2. I want to add the...
2
by: kkshansid | last post by:
$projid=$_GET; $q="INSERT INTO proj select * from projtemp where projid ='".$projid."'"; $qins=mysql_query($q); is resulting false is there any error? there are 2 tables in a database with name...
9
by: NikArt | last post by:
I get syntax errors while trying to copy all data from a query into a table using INSERT INTO and SELECT. Here are the lines: INSERT INTO LoadTBTemp])] SELECT Data1 FROM LoadTB ...
0
by: ankit chauhan | last post by:
If I am using the select , option tag in select box . option tag not support margin and padding in chrome .. Please tell me that solution ....
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.