You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@empire-db.apache.org by andrew cooke <an...@acooke.org> on 2009/12/09 01:00:29 UTC
How do I construct this select (with sub-select column), please?
Hi,
I thought I had done this before, but now I can't work out what I did.
I would like to construct this query, given that the appropriate
tables etc are defined:
select ARTISTS.id, ARTISTS.value
from ARTISTS
left join (select ARTIST_CANONICAL.artist
from ARTIST_CANONICAL
join LFM_ARTIST_TAGS
on ARTIST_CANONICAL.canonical = LFM_ARTIST_TAGS.source) as X
on ARTISTS.id = X.artist
where X.artist is null;
In case it helps, the logic is that I want the rows from ARTISTS that
do not have any link, via the association table ARTIST_CANONICAL, with
LFM_ARTIST_TAGS.
It's all "trivial" except for "ARTISTS.id = X.artist" - I have a
DBCommand that represents X, but it has no attribute for the column.
Somehow I need to convert the command to something that is table-like
(I think).
Thanks,
Andrew
re: How do I construct this select (with sub-select column), please?
Posted by Rainer Döbele <do...@esteam.de>.
Hi Andrew,
yes correct: DBQuery is the answer.
There is an example for subqueries in the advanced sample application provided with the distribution (empire-db-example-advanced). See function querySample(...) in SampleAdvApp.java.
Regards,
Rainer
andrew cooke wrote:
> Re: How do I construct this select (with sub-select column),
> please?
>
> Ah, sorry, this is the same as the prev Query here (use DBQuery).
> Please ignore, Andrew.
>
>
> 2009/12/8 andrew cooke <an...@acooke.org>:
> > Hi,
> >
> > I thought I had done this before, but now I can't work out what I
> did.
> > I would like to construct this query, given that the appropriate
> > tables etc are defined:
> >
> > select ARTISTS.id, ARTISTS.value
> > from ARTISTS
> > left join (select ARTIST_CANONICAL.artist
> > from ARTIST_CANONICAL
> > join LFM_ARTIST_TAGS
> > on ARTIST_CANONICAL.canonical =
> LFM_ARTIST_TAGS.source) as X
> > on ARTISTS.id = X.artist
> > where X.artist is null;
> >
> > In case it helps, the logic is that I want the rows from ARTISTS that
> > do not have any link, via the association table ARTIST_CANONICAL,
> with
> > LFM_ARTIST_TAGS.
> >
> > It's all "trivial" except for "ARTISTS.id = X.artist" - I have a
> > DBCommand that represents X, but it has no attribute for the column.
> > Somehow I need to convert the command to something that is table-like
> > (I think).
> >
> > Thanks,
> > Andrew
> >
Re: How do I construct this select (with sub-select column), please?
Posted by Francis De Brabandere <fr...@gmail.com>.
We should set up a wiki page on converting sql queries to empire-db code.
On Wed, Dec 9, 2009 at 10:15 AM, andrew cooke <an...@acooke.org> wrote:
> Ah, sorry, this is the same as the prev Query here (use DBQuery).
> Please ignore, Andrew.
>
>
> 2009/12/8 andrew cooke <an...@acooke.org>:
>> Hi,
>>
>> I thought I had done this before, but now I can't work out what I did.
>> I would like to construct this query, given that the appropriate
>> tables etc are defined:
>>
>> select ARTISTS.id, ARTISTS.value
>> from ARTISTS
>> left join (select ARTIST_CANONICAL.artist
>> from ARTIST_CANONICAL
>> join LFM_ARTIST_TAGS
>> on ARTIST_CANONICAL.canonical = LFM_ARTIST_TAGS.source) as X
>> on ARTISTS.id = X.artist
>> where X.artist is null;
>>
>> In case it helps, the logic is that I want the rows from ARTISTS that
>> do not have any link, via the association table ARTIST_CANONICAL, with
>> LFM_ARTIST_TAGS.
>>
>> It's all "trivial" except for "ARTISTS.id = X.artist" - I have a
>> DBCommand that represents X, but it has no attribute for the column.
>> Somehow I need to convert the command to something that is table-like
>> (I think).
>>
>> Thanks,
>> Andrew
>>
>
--
http://www.somatik.be
Microsoft gives you windows, Linux gives you the whole house.
Re: How do I construct this select (with sub-select column), please?
Posted by andrew cooke <an...@acooke.org>.
Ah, sorry, this is the same as the prev Query here (use DBQuery).
Please ignore, Andrew.
2009/12/8 andrew cooke <an...@acooke.org>:
> Hi,
>
> I thought I had done this before, but now I can't work out what I did.
> I would like to construct this query, given that the appropriate
> tables etc are defined:
>
> select ARTISTS.id, ARTISTS.value
> from ARTISTS
> left join (select ARTIST_CANONICAL.artist
> from ARTIST_CANONICAL
> join LFM_ARTIST_TAGS
> on ARTIST_CANONICAL.canonical = LFM_ARTIST_TAGS.source) as X
> on ARTISTS.id = X.artist
> where X.artist is null;
>
> In case it helps, the logic is that I want the rows from ARTISTS that
> do not have any link, via the association table ARTIST_CANONICAL, with
> LFM_ARTIST_TAGS.
>
> It's all "trivial" except for "ARTISTS.id = X.artist" - I have a
> DBCommand that represents X, but it has no attribute for the column.
> Somehow I need to convert the command to something that is table-like
> (I think).
>
> Thanks,
> Andrew
>