Sign in to follow this  
Followers 0
Graimer

SQL newbie question :)

10 posts in this topic

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.

Share this post


Link to post
Share on other sites

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]

Share this post


Link to post
Share on other sites


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?

Share this post


Link to post
Share on other sites

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

1 person likes this

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

2 people like this

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Recently Browsing   0 members

    No registered users viewing this page.