CODEWITHSUNDEEP
pythonmysql
Mike C.
Mike C.

Reputation: 1921

Return a tuple based off a subquery using MySQL and Python

This works before I put the CONCAT statement in. I was hoping to return a tuple for the uploads field based off of the subquery. 

def my_query():
    conn = create_connection() 
    cur = conn[0]
    cur.execute("""SELECT d.id, s.val1, d.val2, s.val3, d.val4,
                          r.val5, r.val6, r.val7,
                          CONCAT(SELECT u.id, u.file_name, u.file_path FROM related_docs u WHERE u.icd_id = d.id)
                          AS uploads
                   FROM icd_d d, icd_s s, icd_r r
                   WHERE d.id = s.icd_id
                   AND d.id = r.icd_id
                   ORDER BY id ASC
                """)
    data = cur.fetchall()    
    return data

Upvotes: 0

Views: 202

Answers (1)

forpas
forpas

Reputation: 164089

I think you want this:

(SELECT CONCAT(u.id, u.file_name, u.file_path) FROM related_docs u WHERE u.icd_id = d.id) AS uploads

but it is better to add spaces between each value:

(SELECT CONCAT(u.id, ' ', u.file_name, ' ', u.file_path) FROM related_docs u WHERE u.icd_id = d.id) AS uploads

This will work only if the subquery returns only 1 row.
If there is a case of multiple rows, use GROUP_CONCAT() also to get a comma separated list of each row:

(SELECT GROUP_CONCAT(CONCAT(u.id, ' ', u.file_name, ' ', u.file_path)) FROM related_docs u WHERE u.icd_id = d.id) AS uploads

Upvotes: 2

Related Questions