I have two SQL statements that I just can't seem to join together I provided screen show of both.
Basically I need help INNER JOINing the two tables and I seem to keep on getting an error.
Statement 1 which works fine:
SELECT
[login to view URL],
aa.meta_block_timestamp,
aa.blockchain_transaction_id,
SUM(aa.value_int) FROM (
SELECT DISTINCT
[login to view URL],
transactions.blockchain_transaction_id,
transactions.meta_block_timestamp,
to_val, value_int
FROM accounts INNER JOIN transactions
ON [login to view URL] = [login to view URL]
INNER JOIN blockchain
ON [login to view URL] = transactions.blockchain_transaction_id
INNER JOIN clauses
ON [login to view URL] = clauses.transaction_id
WHERE [login to view URL] = LOWER('0xe0EB562acD5F10A60EB3c37886C58fe90d7086BE')
) as aa
GROUP BY
[login to view URL],
aa.meta_block_timestamp,
aa.blockchain_transaction_id
ORDER BY aa.meta_block_timestamp DESC
returns: [login to view URL]
=================================================================
statement 2 works fine which is:
SELECT * FROM (
SELECT [login to view URL], count(yy.to_val) FROM (
SELECT DISTINCT transaction, clauses.to_val FROM transactions
INNER JOIN clauses
ON [login to view URL] = clauses.transaction_id
WHERE [login to view URL] = LOWER('0x90154fb433a6f2a308fd0b27e50f4a29d22c1cb8c7e4b00cbf608dd0b0019ce8')
) as yy
GROUP BY [login to view URL]
) as kk
and returns [login to view URL]
=================================================================
what I am trying to do INNER JOIN statement1 statement2 via [login to view URL]
I have tried the following code:
SELECT
[login to view URL],
aa.meta_block_timestamp,
aa.blockchain_transaction_id,
COUNT(aa.to_val), SUM(aa.value_int) FROM (
SELECT DISTINCT
[login to view URL],
transactions.blockchain_transaction_id,
transactions.meta_block_timestamp,
to_val, value_int
FROM accounts INNER JOIN transactions
ON [login to view URL] = [login to view URL]
INNER JOIN blockchain
ON [login to view URL] = transactions.blockchain_transaction_id
INNER JOIN clauses
ON [login to view URL] = clauses.transaction_id
WHERE [login to view URL] = LOWER('0xe0EB562acD5F10A60EB3c37886C58fe90d7086BE')
) as aa
INNER JOIN (
SELECT [login to view URL], count(yy.to_val) FROM (
SELECT DISTINCT transaction, clauses.to_val FROM transactions
INNER JOIN clauses
ON [login to view URL] = clauses.transaction_id
WHERE [login to view URL] = [login to view URL]
GROUP BY [login to view URL]
) as yy
) as BB on [login to view URL] = [login to view URL]
GROUP BY
[login to view URL],
[login to view URL],
aa.meta_block_timestamp,
aa.blockchain_transaction_id
ORDER BY aa.meta_block_timestamp DESC
==============================
but I am getting an error of :
ERROR: invalid reference to FROM-clause entry for table "aa"
LINE 1: ...s.transaction_id WHERE [login to view URL] = aa.transac...
^
HINT: There is an entry for table "aa", but it cannot be referenced from this part of the query.
(execution time: 266 ms; total time: 516 ms)
================================
We have expertise in SQL and have done Inner Joining of Tables for many database.
I have read through the requirements and am confident of delivering well.
I need the database/ tables with data to check the issue.
You are highly rated with many projects to your credit and I will consider it a privilege to offer my services to you. I look forward to this project as an opportunity to establish a long term association.
Hey
A SQL expert developer is here
I have good advanced expertise in SQL and Queries
This is an initial bid
Feel Free to message me
Regards
Relevant Skills and Experience
SQL
MySQL Programming
Hey!
Greeting of the dayI!
I am immediately available for the job.
I guarantee high quality work, quick answers on you messages and responsibility.
I would like to do job, looking forward for positive response from your side.
Sincerely,
Hi,
I've read carefully of your job description about "Help with SQL statement..".I am a professional "Full Stack Web Developer".I have more than five years experience in Web Development.
Your satisfaction with the project is my top priority! If you give me a chance to work with you then I will try my best level for your project to run successfully.
Waiting for your kind response.
Best Regards
Tridip Sarkar
Hi,
I've looked at your query, and I believe you can achieve your result set with CTEs (common table expressions).
Could you please give me a few screenshots with the result set of the queries (particularly query 2) so I can analyse further?
Cheers,
Ranjana
● A technocrat with 4.3 years of IT experience in development and support projects, having qualitative & rich experience in Business Intelligence environment.
● Hands on experience with SQL Server and T-SQL in constructing tables, views, indexes.
● Extensive knowledge in writing stored procedures, Functions, appropriate Triggers to facilitate efficient data manipulation and data consistency
● Extract Transform Load (ETL) development using SQL Server Integration Services (SSIS)
● Expertise in generating reports using SQL Server Reporting Services (SSRS).
● Ability to create Drill down, Drill through, Cascading Reports and sub reports in SSRS.
● Worked with integration services for transferring and reviewing data from heterogeneous sources using different Control flow and data flow components in SQL Server Integration Services 20008R2/12/14
● Proven proficiency at Data Flow Transformations like Lookup, Derived Column, Conditional Split, Data Conversion, Union All, Merge Join and OLEDB Command to load data.
● Experience in managing SSIS packages execution and deploying packages to the servers.
● Experience in providing Logging and Error handling by using Event Handler for SSIS Packages.
● Automated the execution of packages by scheduling jobs through SQL server Job agent.
Technical Skills
● Databases : SQL Server 2008 R2/12/14
● ETL Tools : SQL Server Integration Services (SSIS 2008 R2/12/14)
● Reporting Tools : SQL Server Reporting