Error on JOIN

[Eric Torgerson]

Hello,
I am trying to do a simple JOIN in JIQL but getting error:

CREATE TABLE feature (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name
VARCHAR(40) NOT NULL, feature_type_id INT);

CREATE TABLE feature_type (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
alias VARCHAR(20) NOT NULL);


ALTER TABLE feature ADD CONSTRAINT feature_fk1 FOREIGN KEY
( feature_type_id ) REFERENCES feature_type (id);


INSERT INTO feature_type ( alias) VALUES ( 'BLUE');

INSERT INTO feature ( name, feature_type_id) VALUES ( 'SOME_FEATURE',
1);


***Both these SELECT statements fail:


SELECT feature.id , feature.name , feature_type.alias FROM feature ,
feature_type WHERE feature_type.id = feature.feature_type_id;

&&

SELECT feature.id , feature.name , feature_type.alias FROM feature
JOIN feature_type ON feature_type.id = feature.feature_type_id;


What am I doing wrong? Thanks!
Eric

[Gabriel Wong]

Hi Eric,
Sorry for the delayed response. What is the error you are getting?

[Eric Torgerson]

Gabriel,
I am getting this:

[java] feature as Table does NOT Exists jiql.SQLParser
table:feature as;ali
ases:{feature_type=feature as};values:{};selects:
[feature_type.alias];selectAS:{
};selectAS2:{};includealllist:[];eitheroralllist:
[];getOriginalSelectList:[featu
re.id, feature.name,
feature_type.alias];groupby:org.jiql.db.GroupBy@6fa06fa0;un
ion:jiql.Union tables:{feature=jiql.SQLParser table:feature;aliases:
{};values:{}
;selects:[feature.id, feature.name];selectAS:{};selectAS2:
{};includealllist:[SQL
Criteria:id=feature.feature_type_id];eitheroralllist:
[];getOriginalSelectList:[]
;groupby:org.jiql.db.GroupBy@768c768c;union:null};aliases:
{};selects:;selectAS:;
jincludealllist:[];jeitheroralllist:
[];includealllist:;eitheroralllist:

for

SELECT feature.id , feature.name , feature_type.alias FROM feature,
feature_type WHERE feature_type.id = feature.feature_type_id;

and
feature Missing main join dependency

for

SELECT feature.id , feature.name , feature_type.alias FROM feature
JOIN feature_type ON feature_type.id = feature.feature_type_id;

Thanks!
Eric

[Gabe Wong]

what do you get when you do
select * from feature;

> --
> You received this message because you are subscribed to the Google Groups "jiql" group.
> To post to this group, send email to ji...@googlegroups.com.
> To unsubscribe from this group, send email to jiql+uns...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/jiql?hl=en.
>
>

[Eric Torgerson]

For "select * from feature"

I get the expected results

id=1, name=SOME_FEATURE, feature_type_id=1

Same for "select * from feature_type"

id=1, alias=BLUE

If my JOIN syntax is wrong, could you provide an example with CREATEs
and INSERTs and SELECTs that works in JIQL. I am not stuck on the
syntax in my example.
Eric

[Gabriel Wong]

Hi Eric,

SELECT feature.id , feature.name , feature_type.alias FROM

feature ,feature_type WHERE feature_type.id =
feature.feature_type_id;
should now work with the latest jiql.jar:
http://code.google.com/p/jiql/downloads/list

Regards

[Eric Torgerson]

Gabriel,
Yes the SQL:

SELECT feature.id , feature.name , feature_type.alias FROM feature,
feature_type WHERE feature_type.id = feature.feature_type_id;

now works properly. Thanks!

The SQL:

SELECT feature.id , feature.name , feature_type.alias FROM feature

JOIN feature_type ON feature_type.id = feature.feature_type_id;

produces this new error:

feature Missing main join dependency

I am not sure the JOIN SELECT SQL is kosher for JIQL?
Eric

[Gabriel Wong]

Hi Eric,

SELECT feature.id,feature.name,feature_type.alias FROM feature JOIN

feature_type ON feature_type.id = feature.feature_type_id;

should now work with the latest jiql.jar:
http://code.google.com/p/jiql/downloads/list
Regards