SQL CE Statement not working the way I think it should

  • Thread starter Thread starter jayderk
  • Start date Start date
J

jayderk

I have 2 tables with almost the same fields and want to collect data from
both.
DOES NOT WORK
string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns, unsched "
+ "WHERE accepted != 'u'";

WORKS with only one table of course
string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns "
+ "WHERE accepted != 'u'";

in the help for SQL CE it says [,....n] which tells me the first statement
is legal.. but doesn't work..
FROM { < table_source > } [ ,...n ]
< table_source > ::=
table_name [ [ AS ] table_alias ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition >
| ( < joined_table > )
< join_type > ::=
[ INNER | { { LEFT | RIGHT } [ OUTER ] } ] JOIN

thanks in advanced
 
is the filter field unique to only one table? Using TSQL syntax is also a
bit less confusing.
 
Sounds like you are looking for a UNION.

string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns "
+ "WHERE accepted != 'u' "
+ "UNION "
+ "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM unsched "
+ "WHERE accepted != 'u'";

Note that in the SQL CE BOL, it states "The definitions
of the columns that are part of a UNION operation do not
have to be identical, but they must be compatible through
implicit conversion."

You will probably want to look up UNION in the BOL for a
more precise explanation.

Cheers, Ian
 
Where do I find some good documentation on TSQL?

I fixed the problem with the 2 tables. I didn't want the C product.. I only
wanted the union..

regards,
Jay
Chris Tacke said:
is the filter field unique to only one table? Using TSQL syntax is also a
bit less confusing.

--
Chris Tacke, eMVP
Co-Founder and Advisory Board Member
www.OpenNETCF.org
---
Windows CE Product Manager
Applied Data Systems
www.applieddata.net


jayderk said:
I have 2 tables with almost the same fields and want to collect data from
both.
DOES NOT WORK
string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns, unsched "
+ "WHERE accepted != 'u'";

WORKS with only one table of course
string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns "
+ "WHERE accepted != 'u'";

in the help for SQL CE it says [,....n] which tells me the first statement
is legal.. but doesn't work..
FROM { < table_source > } [ ,...n ]
< table_source > ::=
table_name [ [ AS ] table_alias ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type > < table_source > ON < search_condition
Click to expand...
| ( < joined_table > )
< join_type > ::=
[ INNER | { { LEFT | RIGHT } [ OUTER ] } ] JOIN

thanks in advanced
Click to expand...
Click to expand...
 
You might start here:

http://www.devguru.com/Technologies/sqlsyntax/quickref/sql_syntax_intro.html

--
Chris Tacke, eMVP
Co-Founder and Advisory Board Member
www.OpenNETCF.org
---
Windows CE Product Manager
Applied Data Systems
www.applieddata.net


jayderk said:
Where do I find some good documentation on TSQL?

I fixed the problem with the 2 tables. I didn't want the C product.. I only
wanted the union..

regards,
Jay
Chris Tacke said:
is the filter field unique to only one table? Using TSQL syntax is also a
bit less confusing.

--
Chris Tacke, eMVP
Co-Founder and Advisory Board Member
www.OpenNETCF.org
---
Windows CE Product Manager
Applied Data Systems
www.applieddata.net


jayderk said:
I have 2 tables with almost the same fields and want to collect data from
both.
DOES NOT WORK
string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns, unsched "
+ "WHERE accepted != 'u'";

WORKS with only one table of course
string sql = "SELECT _id, "
+ "accepted, "
+ "accepted_date, "
+ "quantity "
+ "FROM returns "
+ "WHERE accepted != 'u'";

in the help for SQL CE it says [,....n] which tells me the first statement
is legal.. but doesn't work..
FROM { < table_source > } [ ,...n ]
< table_source > ::=
table_name [ [ AS ] table_alias ]
| < joined_table >
< joined_table > ::=
< table_source > < join_type > < table_source > ON <
Click to expand...
search_condition
| ( < joined_table > )
< join_type > ::=
[ INNER | { { LEFT | RIGHT } [ OUTER ] } ] JOIN

thanks in advanced
Click to expand...
Click to expand...
Click to expand...
 
Back
Top