CODEWITHSUNDEEP
sqlpostgresqlstring-constant
Cheetaiean
Cheetaiean

Reputation: 941

psql treating text input as column in a Join statement

SELECT DISTINCT midiport_id, mp.implementation, d.device_function, mp.serial, mp.dev, mp.subdev, mc.device_id, mc.target, mc.mask, mf.midi_function_id, kd.divn, mc.dev, mc.channel, d.inputs, d.outputs, mc.lo_key, mc.hi_key, mc.program, mc.case_value, mc.num_value, mc.tab_device_id, mf.status, pd.divn, mp.local_port, mp.console_id, mc.controlling_tab_device_id, mc.controlling_tab_default, mp.digit_offset, mp.digit_size 
FROM midiport mp join console c on c.console_id = mp.console_id and mp.console_id = 652 
JOIN midi_input_rules mc USING (midiport_id) 
JOIN device d ON (d.device_id = mc.device_id) join midi_function mf on mf.name = mc.datasource 
LEFT OUTER JOIN pipe_division kd ON kd.key_division_id = mc.key_division_id 
LEFT OUTER JOIN pipe_division pd ON pd.pipe_division_id = mc.pipe_division_id 
where kd.divn is null or kd.divn = (select min(divn) from pipe_division pdm where mc.key_division_id = pdm.key_division_id)
AND mp.implementation != ‘play’ ORDER BY midiport_id, mc.case_value, mc.channel, mc.target, mc.mask;

If I run the final WHERE condition simply on the midiport table, it works. However, in this JOIN statement for some reason the 'play' brings up a syntax error (column "'play'" does not exist). Why is it treated as a column here when in normal conditions it accepts the value as text?

Upvotes: 0

Views: 47

Answers (1)

Horaciux
Horaciux

Reputation: 6477

‘play’ this is no the right single quotes.

Try this

SELECT DISTINCT midiport_id, mp.implementation, d.device_function, mp.serial, mp.dev, mp.subdev, mc.device_id, mc.target, mc.mask, mf.midi_function_id, kd.divn, mc.dev, mc.channel, d.inputs, d.outputs, mc.lo_key, mc.hi_key, mc.program, mc.case_value, mc.num_value, mc.tab_device_id, mf.status, pd.divn, mp.local_port, mp.console_id, mc.controlling_tab_device_id, mc.controlling_tab_default, mp.digit_offset, mp.digit_size 
FROM midiport mp join console c on c.console_id = mp.console_id and mp.console_id = 652 
JOIN midi_input_rules mc USING (midiport_id) 
JOIN device d ON (d.device_id = mc.device_id) join midi_function mf on mf.name = mc.datasource 
LEFT OUTER JOIN pipe_division kd ON kd.key_division_id = mc.key_division_id 
LEFT OUTER JOIN pipe_division pd ON pd.pipe_division_id = mc.pipe_division_id 
where kd.divn is null or kd.divn = (select min(divn) from pipe_division pdm where mc.key_division_id = pdm.key_division_id)
AND mp.implementation != 'play' ORDER BY midiport_id, mc.case_value, mc.channel, mc.target, mc.mask;

Upvotes: 1

Related Questions