-1

So I am currently in a Database 2 class at my University. We are using the Northwinds db. I haven't used SQL in a few years so I am a little rusty.

I changed a few of the pieces in the Orders table so that instead of 'Germany' it was 'Tahiti'. Now I need to write a query to find which orders shipped where.

I know that I will need to use an Join but I am not exactly sure how. I have gone to W3Schools and looked at the Joins SQL page but still haven't found the correct answer I am looking for.

This is what I have currently (which I am also not sure if it is correct):

SELECT Customers.Country
FROM Customer
WHERE Customer.Country = 'Germany'

INNER JOIN

SELECT Orders.ShipCountry
FROM Orders
WHERE Orders.ShipCountry = 'Tahiti'

So if anyone could give me help I would really appreciate it.

EDIT

So this is the actual question I was given which I think is also kind of poorly worded. "Suspicious e-commerce transactions include orders placed by a customer in one country that are shipped to another country. In fact there are no such orders in the Northwind db, so create a few by modifying some of the "Germany" shipcountry entries in the orders table to "Tahiti". Then write a query which finds orders shipped to a different country from the customer. Hint: in order to do this, you will need to join the Customers and Orders table."

FRALEWHALE
  • 65
  • 2
  • 2
  • 13
  • 1
    When you ask a sql question we need some basics. What is your rdbms, db schema, sample of source data and desire output. That help a lot in getting an answer fast. – Juan Carlos Oropeza Jan 14 '16 at 19:50
  • Sorry. I'm still pretty new here but my friend told me this would be a good place to get some help. All I can really tell you is that my desired output would be to write a query that show which countries I changed. If you need more info I will try my best to get it. – FRALEWHALE Jan 14 '16 at 19:56
  • To make this a bit easier, what do the tables look like (ie. what columns are in the customers table? the orders table?)? Add some sample data from those tables that illustrates what you have (ie. Customers table has a name and country field, so show us that there is a guy named Jim Bob who lives in Canada). Then show us what you want the query to return (show what the rows should contain given your sample data). This will help a lot. – Becuzz Jan 14 '16 at 20:00
  • @FRALEWHALE Its ok, for your reference look at this question.http://stackoverflow.com/questions/34702140/how-to-sum-over-column-with-reset-condition/34702387#34702387 How the data is show in a table format both the current data and desire output – Juan Carlos Oropeza Jan 14 '16 at 20:01
  • @Beccuzz Yes I am trying to find orders that were shipped to different countries than where the customer lives. – FRALEWHALE Jan 14 '16 at 20:12

2 Answers2

1

Is this what you are looking for:

SELECT *
FROM Customer AS C
INNER JOIN
Orders AS O
ON C.CustomerID = O.CustomerID
WHERE C.Country = 'Germany' AND  O.ShipCountry = 'Tahiti';

The above query is based on the Schema as defined in CodePlex

Fuzzy
  • 3,810
  • 2
  • 15
  • 33
-1

i hope that this can help you

SELECT Orders.* 
FROM Orders  -- table name, also can use alias
inner join Customer -- table name, also can use alias
on Orders.ShipCountry = Customer.Country  -- you must declare what is the field to use by join
where Customer.Country in ('Germany','Tahiti')
Becuzz
  • 6,846
  • 26
  • 39
Willians Escate
  • 1
  • Based on the question title, it looks like the OP wants orders that were shipped to different countries than where the customer lives (ie. Customer.Country != Order.ShipCountry). Also, joining on country (and not some sort of FK) will lead to some kinda crazy cross join that won't provide much meaningful data. – Becuzz Jan 14 '16 at 20:04