SQL Query to split result by comma and give result as input of another query

Question

I'm trying to use SQL query where the value of one query is used in another query. Here is my SQL query:

Select * 
from ( select detection_class, detection_class_id, matched_alert_id, stream_id, track_id, detection_time, frame_id 
       from matched_alert 
       where stream_id = %s 
       group by track_id ) 
where (SELECT ',' || detection_class || ',' FROM alerts WHERE alert_id = %s) LIKE '%,' || detection_class || ',%'

this query is stored in query variable. then execute as below:

        place_holders = [stream_id, alert_id]
        try:
            with connection.cursor() as cursor:
                cursor.execute(query, place_holders)
                rows = cursor.fetchall()

shows error:

return sql % params
TypeError: not enough arguments for format string

the result of select detection_class from alerts where alert_id = %s is like 'car,bus,bike' but I need result like 'bus', 'car', 'bike' to give input of where detection_class IN like where detection_class IN ('car', 'bus').

alerts table matched_alert table so, how can I split this result by a comma and make a separate string?

Provide some sample data (as CREATE TABLE + INSERT INTO) and desired output for this data (and some `%s` parameter value - provide it too). — Akina, Aug 09 '21 at 10:09
I *removed* the conflicting tags for a reason... As @DaleK said, [[tag:sql-server]] and [[tag:mysql]] are *completely different* products. Only tag the one you *really* using. — Thom A, Aug 09 '21 at 10:21
Splitting strings into a table or separate columns is probably THE most common topic. Simple web searching will find many suggestions and discussions. — SMor, Aug 09 '21 at 10:22
@Akina this query works when only one detection class like 'car' because matched_alert table has only one detection_class store in one row. — BikashSaud, Aug 09 '21 at 10:22

score 1 · Answer 1 · answered Aug 09 '21 at 10:27

You can side-step the problem entirely with an inner join, thus:

Select * 
from ( select detection_class, detection_class_id, matched_alert_id, stream_id, track_id, detection_time, frame_id 
       from matched_alert 
       where stream_id = %s 
       group by track_id ) subQ
inner join alerts a on a.detection_class = subQ.detection_class and a.alert_id = subQ.stream_id

Incidentally I am not sure what you want a GROUP BY for in your sub-query since you are not using any aggregation functions.

Group by is used because to get data according to track id, this answer works for me. — BikashSaud, Aug 11 '21 at 02:16

forpas · Accepted Answer · 2021-08-11T11:34:01.067

1

Instead of IN you can use the operator LIKE:

WHERE (
  SELECT ',' || detection_class || ','
  FROM alerts 
  WHERE alert_id = ? 
) LIKE '%,' || detection_class || ',%'

and the query as a Python string will be:

query = """
Select * 
from ( select detection_class, detection_class_id, matched_alert_id, stream_id, track_id, detection_time, frame_id 
       from matched_alert 
       where stream_id = ? 
       group by track_id ) 
where (SELECT ',' || detection_class || ',' FROM alerts WHERE alert_id = ?) LIKE '%,' || detection_class || ',%'
"""

edited Aug 11 '21 at 11:34

answered Aug 09 '21 at 10:31

forpas

160,666
10
38
76

query = "Select * from (select detection_class, detection_class_id, stat_type,stream_id, track_id, "\ "detection_time, frame_id from stats where stream_id = %s) where (SELECT ',' || detection_class || ',' FROM alerts WHERE alert_id = %s) LIKE '%,' || detection_class || ',%' " and execute as below: place_holders = [stream_id, alert_id] try: with connection.cursor() as cursor: cursor.execute(query, place_holders) rows = cursor.fetchall() I don't understand what is actual problem is, can you please help me to fix this. – BikashSaud Aug 11 '21 at 10:55
@BikashSaud edit your question and put the code there, – forpas Aug 11 '21 at 10:57
@BikashSaud also add the tag of the database that you use. – forpas Aug 11 '21 at 10:57
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/235880/discussion-between-bikashsaud-and-forpas). – BikashSaud Aug 11 '21 at 11:08
@BikashSaud Replace all `%s` with `?`. – forpas Aug 11 '21 at 11:15
@BikashSaud are stream_id and alert_id strings? – forpas Aug 11 '21 at 11:21
stream_id--> varchar, alert_id--> INTEGER. – BikashSaud Aug 11 '21 at 11:28
@BikashSaud Copy the query from my edited answer as it is. – forpas Aug 11 '21 at 11:34
Now error like: return sql % params ValueError: unsupported format character ',' (0x2c) at index 380 – BikashSaud Aug 11 '21 at 11:41
@BikashSaud did you copy the query as it is? There are no format chars in the string. There are only `?`. – forpas Aug 11 '21 at 11:44
yes I did. this--> query = """ Select * from ( select detection_class, detection_class_id, matched_alert_id, stream_id, track_id, detection_time, frame_id from matched_alert where stream_id = ? group by track_id ) where (SELECT ',' || detection_class || ',' FROM alerts WHERE alert_id = ?) LIKE '%,' || detection_class || ',%' """ – BikashSaud Aug 11 '21 at 11:55
@BikashSaud this query works fine as it is. I tested it. Just execute cursor.execute(query, ["1", "2"]) for example. – forpas Aug 11 '21 at 11:57
yeah, this return none when give static parameter but when I tried by giving actual parameter it return error. – BikashSaud Aug 11 '21 at 12:13
@BikashSaud this means that your parameters are wrong. – forpas Aug 11 '21 at 12:14
problem is same as first, when I give detection_class = 'car' only it return result. But when detection_class='car,bus' it return none now. – BikashSaud Aug 11 '21 at 12:22
how do i fiddle sample data. – BikashSaud Aug 11 '21 at 12:37
Here: https://dbfiddle.uk/?rdbms=sqlite_3.27 Post CREATE TABLE statements and INSERT statements for the sample data. – forpas Aug 11 '21 at 12:42
https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=0315dc7174d7f7aab9f19f226aa125a4 – BikashSaud Aug 11 '21 at 14:40
@BikashSaud for stream_id = '863b1675269e4600ab52a2cf0c418edc' and alert_id = 1 this is the result: https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=5b2543acf90f5142e751c2ac165228d7 – forpas Aug 11 '21 at 15:03
Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/235892/discussion-on-answer-by-forpas-sql-query-to-split-result-by-comma-and-give-resul). – Jean-François Fabre Aug 11 '21 at 15:04

SQL Query to split result by comma and give result as input of another query

2 Answers2