Hopefully simple SQL Query?

[smjohns]

Hi,

I am trying to construct a very simple query from two tables and join the results into one list using SQL. The individual queries are as follows:

SELECT td_cycl_fold.CF_ITEM_NAME AS [Folder Name]
FROM td_cycl_fold
WHERE td_cycl_fold.CF_FATHER_ID=1234;

SELECT td_cycle.CY_CYCLE AS [Folder Name]
FROM td_cycle
WHERE td_cycle.CY_FOLDER_ID=1234;

At the moment I have to run these as separate queries but wonder if there was a way of running both at the same time and combining the results of both in a single column list output.

This is all being done within Access. Any help will be greatly appreciated.

[skhanal]

SELECT td_cycl_fold.CF_ITEM_NAME AS [Folder Name], td_cycle.CY_CYCLE AS [Folder Name 2]
FROM td_cycl_fold
INNER JOIN td_cycle
ON td_cycle_fold.CF_FATHER_ID=td_cycle.CF_FATHER_ID
WHERE td_cycl_fold.CF_FATHER_ID=1234;

[smjohns]

Hi Skhanal,

I tried the sql you suggested but Access confirmed "Syntax error in JOIN operation" and on acknowledging this the td_cycle_fold on the fourth row was highlighted. This is the SQL I tried as some of the names you used above were incorrect:

SELECT td_cycl_fold.CF_ITEM_NAME AS [Folder Name], td_cycle.CY_CYCLE AS [Folder Name 2]
FROM td_cycl_fold
INNER JOIN td_cycle
ON td_cycle_fold.CF_FATHER_ID=td_cycle.CY_FOLDER_ID
WHERE td_cycl_fold.CF_FATHER_ID=3788;

[skhanal]

Try this one without table names


SELECT td_cycl_fold.CF_ITEM_NAME AS [Folder Name], td_cycle.CY_CYCLE AS [Folder Name 2]
FROM td_cycl_fold
INNER JOIN td_cycle
ON CF_FATHER_ID=CY_FOLDER_ID
WHERE td_cycl_fold.CF_FATHER_ID=3788;

[smjohns]

Thanks for your continued help. I am running this query in MS Access 2003. Unfortunately it is now confirming "Join expression not supported". Is such a query really possible in Access?

[skhanal]

INNER JOIN is supported in Access. Check if td_cycl_fold and td_cycle are real table names.

[smjohns]

Hmmm strange. The DB structure is:

Database name = default_step_db
Owner = db
Table names = CYCL_FOLD & CYCLE.

The Access application only links to the DB tables (rather than them being held within Access).

The columns CF_FATHER_ID (Number) and CF_ITEM_NAME (Text) only exists on td_CYCL_FOLD

The columns CY_FOLDER_ID (Number) & CY_CYCLE (Text) only exists on td_CYCLE.

Whilst the data held on Father and Folder ID is similar both tables hold completely different data but there will be occurances when searching for the same Father / Folder ID on both tables will both return values. With other queries, entries may only exist on one or the other.

[skhanal]

What is td_ , use the table names instead

SELECT cycl_fold.CF_ITEM_NAME AS [Folder Name], cycle.CY_CYCLE AS [Folder Name 2]
FROM cycl_fold
INNER JOIN cycle
ON CF_FATHER_ID=CY_FOLDER_ID
WHERE cycl_fold.CF_FATHER_ID=3788;

[smjohns]

Tried as you suggested but Access 2003 (SP3) is still issuing the same message - "Join expression not supported". Is there any other info I could supply you to help understand what is happening?

[skhanal]

Only thing I can think of is line breaks causing problem, try putting the whole query in one line.

[smjohns]

Tried that too and no joy? Is inner join definitely the only option? Had a look at Left / Right or Outer join but could not get these to work?