CODEWITHSUNDEEP
sqlms-access
neeko
neeko

Reputation: 2000

INSERT SELECT SQL from two tables

I have two tables one table tbl_OrderLine with order ID and one tbl_Student with Student ID.

I want to select the OrderLineID from tbl_OrderLine and The StudentID From tbl_Student

This is what I have tried so far:

INSERT INTO tbl_StudentPurchaseFromUnibooks 
  (OrderLineID, StudentID )
SELECT tbl_OrderLine.OrderLineID, 
SELECT tbl_Student.StudentID 
 WHERE tbl_Student.LoggedIn ="Yes";

tbl_OrderLine:

OrderLineID     Price       Qty
1                 5          2

tbl_Student:

StudentID     Name    LoggedIn
1             Joe      Yes

tbl_StudentPurchaseFromUnibooks:

StudentPurchaseID    OrderLineID    StudentID    PurchaseDate
1                     1               1            09/12/2012

Also, does any one know a simple way of mimicking a login in Microsoft Access. As this is only dummy database for a Microsoft Access project security isn't an issue but I just would like to know a way of logging users in. At the moment i update their LoggedIn value to "Yes" if they are logged in but obviously this isn't efficient. If no one is able to help with this I will post as a seperate question later :) Thanks!

Upvotes: 0

Views: 3366

Answers (1)

Stefan
Stefan

Reputation: 1000

You want to define your select as a join instead of two separate SQLs. I am not really sure how MS works, but something like this should do the trick:

INSERT INTO tbl_StudentPurchaseFromUnibooks (OrderLineID,   StudentID )
SELECT tbl_OrderLineID.OrderLineID, tbl_Student.StudentID
from tbl_OrderLineId
left join tbl_Student on ???
WHERE tbl_Student.LoggedIn ="Yes";

I am not sure that your table should really be named tbl_OrderLineID and you would need to know your join condition.

Upvotes: 3

Related Questions