SQL MS ACCESS and thos damn JOINS

[joche]

I'm trying to get a list of all recorded shows including the friendly name of the channel, I'm not extremely sucessfull. ;-)

I get an error running this:
select p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name
from programme as p, recording_schedule as rs
LEFT OUTER JOIN channel as c ON c.oid = rs.manual_channel_oid
where p.oid=rs.oid and rs.status=2

It says the join expression isn't supported.

And a last question, is there such a live-command window for MS-access where I can test run my queries. Now I have to run a small console application to verify my SQL syntax, very time consuming.

Thanks for all help!

[KingArgyle]

Try this:

Try this:

select p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name
from programme as p
inner join recording_schedule as rs on p.oid = rs.programme_oid
LEFT OUTER JOIN channel as c ON c.oid = rs.manual_channel_oid

As for working with Access SQL. If you have MS Access it self you can build your queries in there, and test them that way. Otherwise you haev to use an ODBC/OLEDB tool to do it.

[joche]

Quote[/b] (KingArgyle @ Feb. 16 2005,19:30)]select p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name
from programme as p
inner join recording_schedule as rs on p.oid = rs.programme_oid
LEFT OUTER JOIN channel as c ON c.oid = rs.manual_channel_oid

Thanks for your quick reply. I'm just off to bed (it's 1.30am). Just because I probably will run into problems tomorrow when I test this, where shall I put the (equalence to) " rs.status=2 "?

[jasonf]

Your where clause would still go at the end.

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">SELECT p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name
FROM programme as p
    INNER JOIN recording_schedule as rs ON p.oid = rs.programme_oid
    LEFT JOIN channel as c ON c.oid = rs.manual_channel_oid
WHERE rs.status = 2
[/QUOTE]

[joche]

Quote[/b] (jasonf @ Feb. 16 2005,19:41)]Your where clause would still go at the end.

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">SELECT p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name
FROM programme as p
INNER JOIN recording_schedule as rs ON p.oid = rs.programme_oid
LEFT JOIN channel as c ON c.oid = rs.manual_channel_oid
WHERE rs.status = 2

[/QUOTE]

Thanks, unfortunately I'm still stucked:
<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">
System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression
'p.oid = rs.programme_oid LEFT JOIN channel as c ON c.oid = rs.manual_channel_oid'.
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARA
MS dbParams, Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Ob
ject&amp; executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behav
ior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OleDb.OleDbCommand.ExecuteReader()
at ConsoleApplication1.Class1.Main(String[] args) in c:\visual studio project
[/QUOTE]

[KingArgyle]

What does your source code look like when you are building the SQL string to be executed?

[joche]

Quote[/b] (KingArgyle @ Feb. 17 2005,10:39)]What does your source code look like when you are building the SQL string to be executed?

Ahh, a very relevant question:

This is to build the query:
<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE"> string query = " SELECT p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name";
query += " FROM programme as p";
query += " INNER JOIN recording_schedule as rs ON p.oid = rs.programme_oid";
query += " LEFT JOIN channel as c ON c.oid = rs.manual_channel_oid";
query += " WHERE rs.status = 2";[/QUOTE]

and below is the command:

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE"> OleDbCommand myCommand = new OleDbCommand(query,myConnection);
myConnection.Open();
OleDbDataReader myReader = myCommand.ExecuteReader();[/QUOTE]

[jasonf]

Ah! Oh, yeah.... Access requires parens around a join set if you're going to perform additional joins.

I played around with the query in Access until it worked, and here's the SQL output:

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">SELECT p.name, p.description, rs.filename, rs.manual_start_time, rs.manual_end_time, c.name
FROM (programme AS p INNER JOIN recording_schedule AS rs ON p.oid = rs.programme_oid)
LEFT JOIN channel AS c ON rs.manual_channel_oid = c.oid
WHERE (((rs.status)=2));
[/QUOTE]

[joche]

Yes, that did it. No wonder I never succeeded during my trial and error method.

Thanks jasonf and KingArgyle for your help.