SQL newbie question :)

[Graimer]

Hey. I'm new(started today) With SQL for a school assignment(bad teacher :p ) and I've already met a problem. The task is to take multiple Excel files and create a normalized sql db. Now here's my problem:

I have one table importedOrders which includes the varchar Fields "FirstName" and "LastName" that indicates the employee(!) that is responsible. I also have a table importedEmployees With a EmployeeID, FirstName and LastName. I'm migrating some data from the importedTables into some normalized tables. The table Orderline will need to have an employeeID Foreign key(primary key is in a new Employee table) and in my migration-query, I need to find the right employeeID based on the FirstName and LastName. I got the following:

INSERT INTO Orderline (ProductID, Quantity, .... , EmployeeID)
SELECT ...., (SELECT EmployeeID FROM dblab2.importEmployee E WHERE importOrders.FirstName = E.FirstName AND importOrders.LastName = E.LastName)
[/CODE]

Is this right(can't test it right now) or do I need to join the tables(which kind of join?)? Am I even close? :p Would appreciate all help I can get.

[Kami-]

INSERT INTO OrderLine (ProductID, Quantity, ...., EmployeeID)
SELECT
  importOrders.ProductID
,importOrders.Quantity
,....
,importEmployee.EmployeeID
FROM importOrders
INNER JOIN importEmployee
ON importOrders.FirstName = importEmployee.FirstName
AND importOrders.LastName = importEmployee.LastName
[/CODE]

[Graimer]

INSERT INTO OrderLine (ProductID, Quantity, ...., EmployeeID)
SELECT
  importOrders.ProductID
,importOrders.Quantity
,....
,importEmployee.EmployeeID
FROM importOrders
INNER JOIN importEmployee
ON importOrders.FirstName = importEmployee.FirstName
AND importOrders.LastName = importEmployee.LastName
[/CODE]

Thanks. Works perfect :) Guess I gotta start Reading about all different joins. Is there a way to tell when you need to use subquery and when to use join?

[Kami-]

Many reasons; performance is a big concern for me as to if I use a join, a derived (sub) query, temporary table or CTE.

[Graimer]

OK, I've got another followup question here if you(or anyone else) got time :) How can I Group multiple records to one(per Group)? What I'm gonna do is take all orderlines, and create an 'Order' table for them. But before I could add en OrderID Foreign key to the 'orderline' table I Guess I have to export all orders to a 'Order' table. All orderlines for the same order has identical values in orderdate, buyername, freight(that should be enough i Guess). Is it possible to make a Query that Groups all orderlines that belong together so I could copy out the identical parts of the orderlines to make a record in the new 'Order' table?

Would appreciate all help I could get :) At least a Clue that tells me what to Google :p

EDIT: Is it as easy as this?:

 
SELECT Buyername, freight, ..... , OrderDate
FROM Orderlines
GROUP BY OrderDate, Buyername, freight
ORDER BY OrderDate;
[/CODE]

And is "order by" required by MySQL? I think I read somewhere that the order of Your "Group By" values would determine how it was sorted. Btw, sorry for these dumb questions. Doing this without the sql db since the only copy is at School.

[Kami-]

OK, I've got another followup question here if you(or anyone else) got time :) How can I Group multiple records to one(per Group)? What I'm gonna do is take all orderlines, and create an 'Order' table for them. But before I could add en OrderID Foreign key to the 'orderline' table I Guess I have to export all orders to a 'Order' table. All orderlines for the same order has identical values in orderdate, buyername, freight(that should be enough i Guess). Is it possible to make a Query that Groups all orderlines that belong together so I could copy out the identical parts of the orderlines to make a record in the new 'Order' table?

Would appreciate all help I could get :) At least a Clue that tells me what to Google :p

EDIT: Is it as easy as this?:

SELECT Buyername, freight, ..... , OrderDate
FROM Orderlines
GROUP BY OrderDate, Buyername, freight
ORDER BY OrderDate;
[/CODE]

And is "order by" required by MySQL? I think I read somewhere that the order of Your "Group By" values would determine how it was sorted. Btw, sorry for these dumb questions. Doing this without the sql db since the only copy is at School.

That looks about right; alternatively you can use a SELECT DISTINCT to return the columns you need only once per matching set.

[Graimer]

Nice. :) What do you mean? Isn't the point of Group by to just return one record for each order(matching set).Or did you mean that "SELECT DISTINCT OrderDate, Buyername" would return the same as "Group by"?

EDIT: Oh, right.. I think I understand what you said. Since I just need to remove duplicate rows, I could use Select distinct instead, right? I thoght that would just return a buyername one time(even it it had multiple orderdates), but I Guess that was wrong :p

While I got you here: is this solution the best for my problem? To first export/migrate orderlines to orders and then use the same Query as With employees to link up orderid after, or is there a better solution?

[Kami-]

SELECT DISTINCT <fields> - would return all unique combinations of the fields selected.

[Graimer]

Awesome. This is actually not as hard as it seemed. If only my teacher made SQL sound so simple(yet Advanced and powerful) ^^

Thanks alot for all the help.

[Kami-]

I'm a Senior Developer; I mentor newer developers all the time, so I'm used to making people understand :) - If you have any questions going forward; just let me know I review my messages on here at least 3-4 times a week.