Millkind
asked on
Sql Join Statement
I want to combine the two select statements and use the output to insert into another table.
The following are all three individually.
select tblStudent.dIDNumber,tblSt udent.lLos tCardIdent ifier from tblStudent where tblStudent.lMealPlan between 200 and 297 or tblStudent.dIDNumber in (16734300,43384500)
select tblEvents.achEventName,tbl Events.ach RemoveDate ,bGeneralA ccess,tblE vents.lEve ntNumber from tblEvents where tblEvents.lEventNumber = 601
insert into tblStudentEvents (dIDNumber,lLostCardIdenti fier,lEven tNumber,lT ickets,ach EventName, achRemoveD ate,bGener alAccess)
values (tblstudent.dIDNumber,tbls tudent.lLo stCardIden tifier,tbl events.lEv entNumber, 1,tblevent s.achEvent Name,tblev ents.achEv entName,tb levents.bG eneralAcce ss)
The following are all three individually.
select tblStudent.dIDNumber,tblSt
select tblEvents.achEventName,tbl
insert into tblStudentEvents (dIDNumber,lLostCardIdenti
values (tblstudent.dIDNumber,tbls
You need to use insert as select combination
insert as select combination:
insert into tblStudentEvents
select tblStudent.dIDNumber,tblStudent.lLostCardIdentifier from tblStudent where tblStudent.lMealPlan between 200 and 297 or tblStudent.dIDNumber in (16734300,43384500) join
(select tblEvents.achEventName,tblEvents.achRemoveDate,bGeneralAccess,tblEvents.lEventNumber from tblEvents where tblEvents.lEventNumber = 601) on ...
ASKER
That the problem there is no on, there is not unique id between them.
as per abov e You need something to join on between students and events , please paste the structure of both tables if You need help identifying what attribute is used to join those 2 tables.
I guess s that both SELECT return one row.
You can do
Insert into ...
Select...
From table1 , table2
Where...
You can do
Insert into ...
Select...
From table1 , table2
Where...
ASKER
No one will return multiple rows the other will return only one. I think im starting to see the problems here. For each return of ID and LostCardID I want it to use the same line of
tblEvents.achEventName,tbl Events.ach RemoveDate ,bGeneralA ccess,tblE vents.lEve ntNumber from tblEvents where tblEvents.lEventNumber = 601) for each one. it inserts.
tblEvents.achEventName,tbl
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect. And the explanation link was superb.
select tblStudent.dIDNumber,tblSt