Complex SQL SELECT?

[Chuck]

I'm still learning SQL, and although I have the basics down of using SELECT, I need to perform a query that I don't know how to construct.

I have two tables: awards and potential_awards. They look like this:

CREATE TABLE awards (

  id                    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

  rank_id               INTEGER       DEFAULT NULL,

  name                  VARCHAR(256)  DEFAULT NULL,

  category              VARCHAR(256)  DEFAULT NULL,

  type                  VARCHAR(256)  DEFAULT NULL,

  optional_count        INTEGER       DEFAULT NULL

);



CREATE TABLE potential_awards (

  id                    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

  scout_id              INTEGER       DEFAULT NULL,

  award_id              INTEGER       DEFAULT NULL,

  completed             BOOLEAN       DEFAULT FALSE

);

I have a scout_id and a category. I want to find all of the potential award ids that have the given scout id and are related to award records with the given category name. I think it's going to involve a sub-query, but I'm not sure.

If it makes a difference, I'm using SQLite right now.

Any assistance would be greatly appreciated.

Thanks,

Chuck

[mr_vodka]

Hi Chuck.

From your description, it doesnt seem as though you need a sub-query; actually it seems like a straight forward Inner join.

SELECT potential_awards.id 

FROM potential_awards

INNER JOIN awards ON potential_awards.id = awards.id

WHERE potential_awards.scout_id =  22 /* or whatever ID you pass*/ 

AND awards.category = 'YourAwardCategory'

[Chuck]

Straightforward when you know SQL. : Thanks very much. I haven't really covered inner joins in SQL yet. I'm reading through Beginning SQL to educate myself.

FYI, I had also posted this question to another forum, and I received an answer which also appears to work, but doesn't use the inner join. The suggested query was:

SELECT potential_awards.id

FROM potential_awards, awards

WHERE potential_awards.scout_id = '1'

AND awards.category = 'Other'

AND potential_awards.award_id = awards.id

This seems to have the exact same results as your suggestion:

SELECT potential_awards.id

FROM potential_awards

INNER JOIN awards

ON potential_awards.award_id = awards.id

WHERE potential_awards.scout_id = 1

AND awards.category = 'Other'

So my question is, is one of them better to use than the other for some reason?

Thanks,

Chuck

[mr_vodka]

Both of them will work.

The other one doesnt explicitly create a join but its requirement makes it so that the IDs match each other. Its kind of a way of cheating the proper way.

The formal way is the way I posted it. You can always modify it to use a different type of join.