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
coolman is currently offline  coolman
Messages: 41
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
Previous Topic: Sql Lite schema with DATE type
Next Topic: postgresql mingw
Goto Forum:
  


Current Time: Sun May 26 13:14:12 CEST 2019

Total time taken to generate the page: 0.00715 seconds