Using Joins with SQL in CR-8.5

[Halfcan]

Hi, I'm using CR-8.5 with 1 SQL DB.
My DB has 2 tables. Table1 is my main table, and Table2 is my lookup table which supplies additional information, (kind of a Master lookup list)
I'm linking from Table1 to Table2 by a common string field.
Table1.ip --> Table2.ip

Table1 has 100 records, Table2 has 500 records.
my problem is that when I run a report I only get the 100 records from Table2 that have matches in Table1.

I need to be able to get a count of 500 from Table2 to use in calculations no matter how many records Table1 has.

I think I need a Right Outer Join, but get this error message:

ODBC error: [MySQL][ODBC 3.51 Driver][mysqld-4.0.16-standard] You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT OUTER JOIN 'databasename'. 'TableName' TableName

I can do a Left outer join without getting the error, but it does change my output like I need above.

Any Ideas?
Thanks,
HC

 

[Halfcan]

NOTE - LAST SENTENCE SHOULD READ: I can do a Left outer join without getting the error, but it does NOT change my output like I need above.
HC

 

[Halfcan]

I did some more testing:
I've rebuilt the sql tables, and replaced the data. I'm using tab delimited file saved from excell.
This is my create table syntax:
create table ATTDEVICELIST (Customer VARCHAR (20), Region VARCHAR (30), Network VARCHAR (30), Location VARCHAR (50), IP_Address VARCHAR (30),
Device_Type VARCHAR (50), Vendor VARCHAR (50), Size VARCHAR (50), Date_Added DATE NOT NULL, Date_Removed DATE NOT NULL,);

I'm very new to sql, so if anyone can find problems with my table syntax that might explain it.

I've testing with blank report in v9.2 and get the same
message but not until I place a field from TABLE2.

In v9.2, also get another message: Failed to open rowset: Details: 42000:[MySQL][ODBC 3.51 Driver][mysqld-4.0.16-standard] You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'RIGHT OUTER JOIN 'databasename'. 'TableName' 'TableName' ON'

any help greatly appreciated,
HC

 

[lbass]

The left outer join should be from Table2 to Table1, with no selects on Table1 is you want to return all Table2 records. I cannot comment on your syntax per se.

-LB