Overview
Examples
Screenshots
Comparisons
Applications
Download
Documentation
Tutorials
Bazaar
Status & Roadmap
FAQ
Authors & License
Forums
Funding Ultimate++
Search on this site
Search in forums












SourceForge.net Logo
Home » U++ Library support » U++ SQL » Sqlite join from select
Sqlite join from select [message #51745] Sun, 12 May 2019 16:18 Go to next message
coolman is currently offline  coolman
Messages: 47
Registered: April 2006
Location: Czech Republic
Member
Hi,

How should I rewrite this SQL command to SqlExp for Sqlite?

SELECT a.*
FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username
AND a.email = b.email
ORDER BY a.email


This one doesn't work:
Select(a(SqlAll()))
.From(users.As(a))
.InnerJoin(Select(Count(SqlAll()), username, email)
	.From(users)
	.GroupBy(username, email)
	.Having(Count(SqlAll()) > 1)
	.AsTable(b))
.On(a(username) = b(username) && a(email) = b(email));


Generated code is:
select a.* from users as a inner join ((select count(*), username, email from users group by username, email having count(*) > 1) as b) on a.username == b.username and a.email == b.email;


But during execution I got `ERROR no such column: b.username ...`

The right command should be (without one bracket before select count(*) and one bracket after b
select a.* from users as a inner join (select count(*), username, email from users group by username, email having count(*) > 1) as b on a.username == b.username and a.email == b.email;


If I rewrite SqlExp to
Select(a(SqlAll()))
.From(users.As(a))
.InnerJoin(Select(Count(SqlAll()), username, email)
	.From(users)
	.GroupBy(username, email)
	.Having(Count(SqlAll()) > 1))
	.AsTable(b)
.On(a(username) = b(username) && a(email) = b(email));


It can not be compiled because the error `no member named 'On' in 'Upp::SqlSet'`

Thank you, Radek
Re: Sqlite join from select [message #51808 is a reply to message #51745] Fri, 07 June 2019 09:41 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 12105
Registered: November 2005
Ultimate Member
So it is one level of parentheses more than it should be?
Re: Sqlite join from select [message #51810 is a reply to message #51808] Fri, 07 June 2019 10:11 Go to previous messageGo to next message
coolman is currently offline  coolman
Messages: 47
Registered: April 2006
Location: Czech Republic
Member
Yes
Re: Sqlite join from select [message #51945 is a reply to message #51810] Sun, 23 June 2019 13:46 Go to previous messageGo to next message
mirek is currently offline  mirek
Messages: 12105
Registered: November 2005
Ultimate Member
Hopefully fixed. Sorry for the delay...

Mirek
Re: Sqlite join from select [message #51948 is a reply to message #51945] Mon, 24 June 2019 08:23 Go to previous message
coolman is currently offline  coolman
Messages: 47
Registered: April 2006
Location: Czech Republic
Member
No problem, thank you
Previous Topic: Escape string in MySQL
Next Topic: SQLite On delete cascade .sch ?
Goto Forum:
  


Current Time: Fri Nov 22 08:30:37 CET 2019

Total time taken to generate the page: 0.01507 seconds