Query not executing. Please help

Hi,
I’m trying to select values whose location matches “\\zxy\sj\projects\str_audio1\MPEG_TS\NEW_STRUCTURE\AACHE” this path. But the query is giving Empty set, but values does exists in the path mentioned. I don’t know, whats happening to the query. Please help.

select s.stream_name,s.stream_location from stream s inner join audio on (s.stream_id=audio_sid) inner join video on (s.stream_id=video_sid) inner join pcr_info on (s.stream_id=pcr_sid) where s.stream_location like '%\\\\zxy\\sj\\projects\\str_audio1\\MPEG_TS\\NEW_STRUCTURE\\AACHE%';

thanks,
Ramki

You need to double all the \ as that is an escape character and so needs to be escaped to give it back its usual meaning so

%\\\\zxy\\sj\\projects\\str_audio1\\MPEG_TS\\NEW_STRUCTURE\\AACHE%

Hi felgall,
Thanks for the reply.
I used the double slash as “\\”, but still i’m not able to get any result.

my first question is: why would a single stream have a matching row in the audio table, and a matching row in the video table, and a matching row in the pcr_info table (whatever that may be)?

so i thought… hey, try it with LEFT OUTER JOINs instead

but then, you aren’t actually returning any columns from any of those other three tables, so why even bother joining to them?

could you explain what the table relationships mean?

Hi 937,
Thanks for the reply.
I actually removed all the things selecting from audio,video,etc tables and the only problem i have in the like clause! I’m able to select other column values with the same like clause but now this value which i gave! Please help.

is this mysql? if so, we should ask for this thread to be moved to the mysql forum

if it is not mysql, then none of the following applies

i would simply use a different escape character, one that is guaranteed not to be used as an actual text character

the problem with the mysql default escape character, the backslash, is that it occurs naturally within your string, so that it needs to be escaped (which is usually done by doubling)

try this –


WHERE s.stream_location LIKE '%\\\\zxy\\sj\\projects\\str_audio1\\MPEG_TS\\NEW_STRUCTURE\\AACHE%' [COLOR="Blue"]ESCAPE '#'[/COLOR]

why are there two consecutive backslashes at the front of the path? that’s not a valid path, is it?

well r937, i tried your trick but bad luck didn’t succeed and the same Empty set resulted.
Here is what i used:

mysql> select s.stream_name,s.stream_location,a.audio_type from stream s left outer join audi
o a on (s.stream_id=a.audio_sid) left outer join video on (s.stream_id=video_sid) le
ft outer join pcr_info on (s.stream_id=pcr_sid) where s.stream_location like '%\\
\\xyz\\blra\\projects\\str_audio1\\MPEG_TS\\NEW_STRUCTURE\\AACHE%' ESCAPE '#' ;
Empty set (0.02 sec)

The two backslash in the front points to a server where file is stored and its valid file path.

would you kindly dump a few rows of sample data from the stream table

R937,

I found an alternate solution and it worked. Thanks for your co-operation.
I used:

select s.stream_name,s.stream_location,a.audio_type from stream s left ou
ter join audio a on (s.stream_id=a.audio_sid) left outer join video on (s.stream
_id=video_sid) left outer join pcr_info on (s.stream_id=pcr_sid) where a.audio_t
ype='AAC' and s.stream_location like '%AACHE%';

Where i first selected all the values with audio_type as AAC and found all values pointing to the same “New_Structure/etc.,” location. So it was easy to execute the above select statement. :slight_smile:

Ramki.