PyMySQL Syntax when using two tables

Question

I have 2 mysql tables:

-Attendance:

att id      | child id
--------------------------
A0012       | C002
A0034       | C001
A0064       | C003
A0065       | C003
A0066       | C003

-Day Prices:

att id      | price  | date
--------------------------
A0012       | 30.00  | 2015-01-26
A0034       | 45.00  | 2015-01-26
A0064       | 62.50  | 2014-12-30
A0065       | 20.0   | 2015-01-02
A0066       | 90.0   | 2015-01-03

I am using pymysql to select the 'price' and 'data' columns from the 'Day Prices' table. However, I would only like such data that corresponds to 'child id' that is 'C003'. This is so that I should obtain only:

 price  | date
--------------------
 62.50  | 2014-12-30
 20.0   | 2015-01-02
 90.0   | 2015-01-03

This is my query:

"SELECT price, date FROM Day Prices WHERE att id FROM Attendance = att id FROM Day Prices WHERE child id = 'C003'"

This query doesn't work so any help coming up with a solution is much appreciated!

score 0 · Accepted Answer · answered Apr 04 '15 at 20:34

0

How about

select d.price, d.date FROM `Day Prices` d
join Attendance a 
on a.att id = d.att id
where a.child id = 'C003';

answered Apr 04 '15 at 20:34

Abhik Chakraborty

44,654
6
52
63

score 0 · Answer 2 · answered Apr 04 '15 at 20:35

The syntax of your query is incorrect. You need to join the tables by att id:

SELECT 
    price, date 
FROM 
    `Day Prices` as dp, `Attendance` as a
WHERE 
    dp.`att id` = a.`att id` AND 
    a.`child id` = 'C003'

Using backticks (`) because of the spaces in table and column names.

score 0 · Answer 3 · answered Apr 04 '15 at 20:35

0

You should use backticks (`) when you are using a table name with spaces:

SELECT price, date FROM `Day Prices`, Attendance 
WHERE `Day Prices`.att_id = Attendance.att_id 
      AND Attendance.child_id = 'C003'

answered Apr 04 '15 at 20:35

Selcuk

57,004
12
102
110

score 0 · Answer 4 · answered Apr 04 '15 at 20:35

You have a couple of problems. One is your use of spaces and reserved words (date) in your table column names; this makes writing your queries a little harder. The other is that you need to learn about JOIN.

This query should work for you.

 SELECT d.price, d.`date`
   FROM `Day Prices` d
   JOIN Attendance a ON d.`att id` = a.`att id`
  WHERE a.`child id` = 'C003'

PyMySQL Syntax when using two tables

4 Answers4