Query from two tables

Question

I have to combine datas from two tables i have created in mySQL.

im supposed to figure out how many of each product that has arrived on any given day.

I have created the following query:

SELECT SUM(Amount) Shipment.Arrival_date, 
       Shipment.Shipment_ID, 
       `Product shipment`.Product_Code, 
       `product shipment`.Shipment_ID, 
       `product Shipment`.Amount 
FROM `Product shipment`, `shipment` 
WHERE `product Shipment`.Shipment_ID=`Product Shipment`.Product_code 
    AND Arrival_date = '2014-01-01

My two respective tables are called Shipment and Product Shipment.

Furthermore i have in my shipment table a collumn that describes the day of arrival, and the shipment_ID which is a primary key to a shipment_ID in my product shipment table.

In my product shipment table, the shipment_ID is described by a product_code and the Amount of the product.

My query returns with an #1064 error. Any suggestions on how to solve?

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.Arrival_date, Shipment.Shipment_ID, `Product shipment`.Product_Code, `product s' at line 1 — Avacay, May 06 '14 at 12:33
AND Arrival_date = '2014-01-01 don't you forgot the last quote? — Anthony Raymond, May 06 '14 at 12:33
`SUM(Amount) Shipment.Arrival_date` is wrong. You probably want to write `SUM(Amount) AS sum_amt, Shipment.Arrival_date`, and also include last quote as anthony has mentioned — Ejaz, May 06 '14 at 12:34
the only thing i could imagine i forgot was a group quote? otherwise im not sure. — Avacay, May 06 '14 at 12:35
hmmm i tried to do what you said. however im getting yet another error, Something with Token Disagreement.. All the datas returned are NULL — Avacay, May 06 '14 at 12:37
You will likely get some really good value from [reading this Q&A that I posted](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) for just situations like this. — Fluffeh, May 06 '14 at 12:53

score 0 · Accepted Answer · answered May 06 '14 at 12:36

0

try this :

SELECT SUM(ps.Amount),
    s.Arrival_date, 
    s.Shipment_ID, 
    ps.Product_Code, 
    ps.Shipment_ID, 
    ps.Amount 
FROM 
    `Product shipment` AS ps,
    `shipment` AS s
WHERE 
    ps.Shipment_ID=ps.Product_code 
    AND 
        s.Arrival_date = '2014-01-01'

Notice that your join link ps table to ps table, it may be a wanted behavior but you never join ps to s

answered May 06 '14 at 12:36

Anthony Raymond

7,434
6
42
59

this one just returns with NULL :( im very new to this, and dont seem to spot the error. – Avacay May 06 '14 at 12:43
You are using two tables, but you NEVER join the tables, you must say add ps.field = s.field in the where clause – Anthony Raymond May 06 '14 at 12:44
the following clause you stated is unknown to me, im afraid i havent learned it yet. :( what does it exactly describe? – Avacay May 06 '14 at 12:46
look at this site : http://technet.microsoft.com/en-us/library/aa213235(v=sql.80).aspx – Anthony Raymond May 06 '14 at 12:47

score 0 · Answer 2 · answered May 06 '14 at 12:37

0

At least you are missing either a comma between SUM(Amount) and Shipment.Arrival_date or missing an 'as' from the same spot. In addtition, Arrival_date in WHERE clause is missing an ' - is it in code or did you just forget to paste it here?

Also, depending on your MySQL settings and OS where MySQL is running on, differencies in table.column name capitalization could cause problems. You have 'Product Shipment

answered May 06 '14 at 12:37

jylipaa

111
8

im running the SQL on OS/X. however i posted the code i used, however after correcting it, it manages to return with what i need, but with null values :( – Avacay May 06 '14 at 12:45

score 0 · Answer 3 · edited May 23 '17 at 12:05

You have missed a comma after the aggregate function in your query. Additionally you had the column you were trying to sum in the select field, which (almost always) is wrong.

This should work:

select
    sum(ps.Amount),
    // You forgot the comma after this aggregate function
    sh.Arrival_date, 
    sh.Shipment_ID, 
    ps.Product_Code, 
    ps.Shipment_ID
from 
    `Product shipment` ps
        join `shipment` sh
            on ps.Shipment_ID=.Product_code 
where
    Arrival_date = '2014-01-01'
group by
    sh.Arrival_date, 
    sh.Shipment_ID, 
    ps.Product_Code, 
    ps.Shipment_ID

Also, you really should read this lengthy Q&A that I put together to help in cases exactly like this, where you have an issue with your query you don't understand, some folks come to help - and while the answers here might solve the issue (we hope) you still don't really get what you did wrong.

thank you for the reply. i still unfortunately get the same error, i will try to read on it abit.. it shouldnt be complicated to solve! Otherwise i must ask my professor.. :) — Avacay, May 06 '14 at 14:02

Query from two tables

3 Answers3