Two possible solutions: A) pure sql ... (warning: untested) Start with all the possible col1 values, and then left join to the other tables, and pick the first col2 found.
select col1, coalesce( A.col2, B.col2, C.col2 ) as col2 from ( select distinct col1 from ( select col1 from A union select col1 from B union select col1 from C ) as u ) as tmp left join A using (col1) left join B using (col1) left join C using (col1) order by col1 ; B) This is potentially very easy at the application level ... here's a perl/DBI example (also untested): my %pairs = map { %{ $dbh->selectall_hashref("select col1, col2 from $_", 'col1') } } reverse qw/ A B C /; # This next line is optional, if you want the hash values to be col2's instead of hashrefs: $_=$_->{col2} for values %pairs; --david On Tue, Mar 24, 2009 at 5:36 PM, Matthew L. Creech <mlcre...@gmail.com> wrote: > Hi, > > I'm hoping someone here can help me out with a query. I have multiple > tables, each with the same schema. For example: > > ========= > Table A: > ========= > 1|"xxx" > 2|"yyy" > 3|"zzz" > ========= > > ========= > Table B: > ========= > 1|"xxx222" > 3|"zzz222" > 5|"www" > ========= > > I'd like a SELECT statement that yields: > > ========= > Result: > ========= > 1|"xxx" > 2|"yyy" > 3|"zzz" > 5|"www" > ========= > > In other words, I want the UNION of all the input tables, but if there > are multiple results that have the same value in the first column, the > first table's value should take precedence. > > This seems like a common scenario, so I'm probably missing something > trivial. :) But so far, the only way I've figured out to do this is > with something like: > > SELECT * FROM > (SELECT 1 AS precedence, col1, col2 FROM A UNION > SELECT 2 AS precedence, col1, col2 FROM B > ORDER BY col1 ASC, precedence DESC) > GROUP BY precedence > ORDER BY col1 ASC; > > (Just an example, I've got several other columns that have to be > sorted on, and there can be any number of tables). This seems to do > what I want, but it takes an order of magnitude longer than the inner > SELECTs do on their own (i.e. without the GROUP BY which eliminates > rows with duplicate 'col1' values). Any ideas on how I could do this > more efficiently? > > Thanks! > > -- > Matthew L. Creech > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users