sqlite question

I have made the following work here:

~$ sqlite3 hi
sqlite> create table matches(id integer primary key autoincrement, person1 varchar, person2 varchar); sqlite> create table people(id integer primary key autoincrement, name varchar, rank varchar);
sqlite> insert into people(name, rank) values ('Payne', 'Major');
sqlite> insert into people(name, rank) values ('Error', 'General');
sqlite> insert into matches (person1, person2) values ('Payne', 'Error');
SELECT A.ID, A.PERSON1, A.PERSON2, B.NAME AS 'NAME1', C.NAME AS 'NAME2' FROM MATCHES AS A INNER JOIN PEOPLE AS B ON B.NAME = A.PERSON1 INNER JOIN PEOPLE AS C ON C.NAME = A.PERSON2 WHERE A.ID=1;
1|Payne|Error|Payne|Error

My guess would be that your schema is not what you presume it to be - the column may very well not exist.

Sorry I wasn't clear in how I want this to work. Matches' person1 and person2 are numbers, representing seeds, so each row would imply 1 v 4, 2 v 3, etc. The people table has each persons ID as their seed. I want to be able to take a table of people ordered from 1st seed to nth seed,and using a previously generated table of matches to display the list of matches, in order, with each seeds name in addition to their seed.

So for People
1, Doug
2, John
3, Bob
4, Kev

and Matches
1, 1, 2 (where the first 1 is just the id/ the primary key?)
2, 3, 4

I could display
"1 Doug v 2 John"
"3 Bob v 4 Kev"

If that makes sense.

Thanks for your help.

You were totally right and I had given the columns slightly different names. However now I have a new problem. Right now I have

<assign property="datasource:matchesdb-list.query" value="SELECT A.ID, A.PERSON1, A.PERSON2, B.FULLNAME AS 'NAME1', C.FULLNAME AS 'NAME2' FROM MATCHES AS A INNER JOIN PEOPLE AS B ON B.FULLNAME = A.PERSON1 INNER JOIN PEOPLE AS C ON C.FULLNAME = A.PERSON2 WHERE A.ID=1;"/>

<alert message="[datasource:matchesdb-list.dataSourceResultCount]"/>

But the alert is always displaying "0" and the rest of my code which creates the feed also displays nothing. Any suggestions would be appreciated. Thanks.

put your alert message in an action like this:

<action name="matchesdb-list-done" datasource="matchesdb-list" datasourceevent="querycomplete">
	<alert message="[datasource:matchesdb-list.dataSourceResultCount]"/>
<action>

This will make sure that the query is completely done before throwing the alert. 

From a WIRE perspective, you can check out this tutorial to give you an idea of how to harness a list and object tags to display this data in your app. 

https://studio.rarewire.com/wordpress/2012/10/sql-snippet/

Maybe this stackoverflow topic on SQL and aliases might be helpful? 

http://stackoverflow.com/questions/6210289/take-column-username-in-each-row-and-split-each-username-into-player-1-player-2/6211060#6211060

That is similar though in my case there is a predetermined matchup order represented in the matches table. So the matches table is already fully populated going into this select call. I am hoping to use that select & join and put it into the matches-listdb datasource and then display that like in the wire datasource tutorial.

I think something like this
http://stackoverflow.com/questions/8160671/sqlite-...
is closer to what I am doing.

I will try working on this again tonight and let you know if any of the suggestions helped. I am a bit worried that I may have left out autoincrementing the id or somehow made it not unique.

Thanks.