Avatar of Millkind
MillkindFlag for Afghanistan 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,tblStudent.lLostCardIdentifier from tblStudent where tblStudent.lMealPlan between 200 and 297 or tblStudent.dIDNumber in (16734300,43384500)

select tblEvents.achEventName,tblEvents.achRemoveDate,bGeneralAccess,tblEvents.lEventNumber from tblEvents where tblEvents.lEventNumber = 601

insert into tblStudentEvents (dIDNumber,lLostCardIdentifier,lEventNumber,lTickets,achEventName,achRemoveDate,bGeneralAccess)
values (tblstudent.dIDNumber,tblstudent.lLostCardIdentifier,tblevents.lEventNumber,1,tblevents.achEventName,tblevents.achEventName,tblevents.bGeneralAccess)
Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Millkind

8/22/2022 - Mon
oleggold

insert into tblStudentEvents
select tblStudent.dIDNumber,tblStudent.lLostCardIdentifier from tblStudent where tblStudent.lMealPlan between 200 and 297 or tblStudent.dIDNumber in (16734300,43384500)
oleggold

You need to use insert as select combination
oleggold

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 ...

Open in new window

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER
Millkind

That the problem there is no on, there is not unique id between them.
oleggold

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.
Guy Hengel [angelIII / a3]

I guess s that both SELECT return one row.
You can do
Insert into ...
Select...
From table1 , table2
Where...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
Millkind

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,tblEvents.achRemoveDate,bGeneralAccess,tblEvents.lEventNumber from tblEvents where tblEvents.lEventNumber = 601) for each one. it inserts.
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Millkind

Perfect. And the explanation link was superb.