Re: [sqlite] How should I use parenthesis?

[Darren Duncan]

On 1/27/2014, 9:57 AM, Jean-Christophe Deschamps wrote:

I'm trying to find the correct syntaxt for this, but I hit a syntax error each
time: either SQLite shokes on outer parenthesis or on union all.


Try something like this, which is a minimal change from yours:

(select * from
(select * from A where x in (subselectA)) dx
left outer join
(select * from B where y in (subselectB)) dy using (...)
)
union all
(select * from
(select * from B where y in (subselectC)) dx
left outer join
(select * from A where x in (subselectD)) dy using (...)
)

... but replace the "using (...)" with a join condition saying which fields you 
want to be used for matching in the join, and also replace the "select *" with a 
specific list of fields you want to match up for the union.


-- Darren Duncan

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use parenthesis?

[Jean-Christophe Deschamps]

Perhaps you want:

select * from (
   select * from A where x in (subselectA)
   left outer join (
select * from B where y in (subselectB)
   ) as a
   on -- something
) as A1

  UNION ALL

select * from (
   select * from B where y in (subselectC)
   left outer join (
select * from A where x in (subselectD)
   ) as b
   on -- something
) as B1

JOIN takes tables (or table-like objects) as operands.  SELECT does not
yield a table unless it's wrapped in parenthesis.

(I generally put UNION in uppercase to make it stand out, lest on a
quick scan it seem like two separate statements.)


Yes that's it. In fact the actual statement is much more complex than 
the sketch I typed, where I forgot to type the where condition and the 
outer select. And I got lost in nesting the parenthesis when 
"simplifying" (i.e. emasculating) my own example.


Thanks for the answers and sorry for the noise. I now have both eyes 
wide open...


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use parenthesis?

[Hick Gunter]

Select A.*,B.* from A left outer join B on x in (subselectA) and y in 
(subselectB)
Union all
Select A.*,B.* from B left outer join A on x in (subselectD) and y in 
(subselectC)

It seems strange that there is no condition limiting which rows from A and B 
match, which makes the LEFT OUTER JOIN rather pointless...

-Ursprüngliche Nachricht-
Von: Jean-Christophe Deschamps [mailto:j...@antichoc.net]
Gesendet: Montag, 27. Jänner 2014 18:57
An: sqlite-users@sqlite.org
Betreff: [sqlite] How should I use parenthesis?

Dear list,

I'm trying to find the correct syntaxt for this, but I hit a syntax error each 
time: either SQLite shokes on outer parenthesis or on union all.

(
select * from A where x in (subselectA)
left outer join
select * from B where y in (subselectB)
)

union all

(  -- <-- error
select * from B where y in (subselectC)
left outer join
select * from A where x in (subselectD)
)

Union [all], except, intersect don't seem to accept parenthesis around the left 
or right parts.

Of course a workaround could be to create two views then union all them, but 
there must be a way to express this construct in a single statement.

I don't want the statement to be interpreted this way:

select * from A where x in (subselectA)
left outer join
(
select * from B where y in (subselectB)
union all
select * from B where y in (subselectC)
)
left outer join
select * from A where x in (subselectD)

--
jcd

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


--
 Gunter Hick
Software Engineer
Scientific Games International GmbH
Klitschgasse 2 – 4, A - 1130 Vienna, Austria
FN 157284 a, HG Wien
Tel: +43 1 80100 0
E-Mail: h...@scigames.at

This e-mail is confidential and may well also be legally privileged. If you 
have received it in error, you are on notice as to its status and accordingly 
please notify us immediately by reply e-mail and then delete this message from 
your system. Please do not copy it or use it for any purposes, or disclose its 
contents to any person as to do so could be a breach of confidence. Thank you 
for your cooperation.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use parenthesis?

[James K. Lowden]

On Mon, 27 Jan 2014 18:57:26 +0100
Jean-Christophe Deschamps  wrote:

> (
> select * from A where x in (subselectA)
> left outer join
> select * from B where y in (subselectB)
> )
> 
> union all
> 
> (  -- <-- error
> select * from B where y in (subselectC)
> left outer join
> select * from A where x in (subselectD)
> )

Perhaps you want:

select * from (
   select * from A where x in (subselectA)
   left outer join (
select * from B where y in (subselectB)
   ) as a
   on -- something
) as A1

  UNION ALL

select * from (
   select * from B where y in (subselectC)
   left outer join (
select * from A where x in (subselectD)
   ) as b
   on -- something
) as B1

JOIN takes tables (or table-like objects) as operands.  SELECT does not
yield a table unless it's wrapped in parenthesis.  

(I generally put UNION in uppercase to make it stand out, lest on a
quick scan it seem like two separate statements.)  

HTH.

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] How should I use parenthesis?

[Igor Tandetnik]

On 1/27/2014 12:57 PM, Jean-Christophe Deschamps wrote:

I'm trying to find the correct syntaxt for this, but I hit a syntax
error each time: either SQLite shokes on outer parenthesis or on union all.

(
select * from A where x in (subselectA)
left outer join
select * from B where y in (subselectB)
)


This part already doesn't make sense. "left outer join" can only appear 
in the FROM clause - but that ends at the WHERE keyword. I can't even 
begin to guess what you are trying to do here. What exactly are you 
joining to what else, and on what condition?

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users