left join to insert

Question

left join to insert

arkiboys 9,711

hello,
I would like to return all rows from "Logins" which are not present in "tbl_LoginIdCount"
It's just that I run this once and insert into compute."tbl_LoginIdCount"
but then when I run the same query again the select still returns the same rows and does not see tha tthose rows are already in tbl_LoginIdCount

select 
    CURRENT_DATE , count(fl."LoginId"), fl."LatestCode" 
from 
    "Logins" as fl
    left join "tbl_LoginIdCount" as lc on date(fl."LoginTimestamp") = lc."Date" and fl."LatestCode" = lc."Code"
WHERE
    lc."Date" is NULL and lc."Code" is null
group by 
    fl."LatestCode";

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-20T22:01:20.6266667+00:00

For type of question, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with sample data, enough to illustrate all angles of the problem. We also like to see the expected results given the test data.

This helps to clarify your question, and it makes it easy to copy and paste into a query window to develop a tested solution.

Beware that any solution you get here is likely to be for SQL Server. The appearance of CURRENT_DATE in your query, makes me believe that you are running something else And in that case, it may be better to find a forum dedicated to your product.

Answer accepted by question author

0 additional answers

Your answer

Erland Sommarskog 134.7K Reputation points MVP Volunteer Moderator

2023-09-20T22:01:20.6266667+00:00

For type of question, we recommend that you post CREATE TABLE statements for your tables and INSERT statements with sample data, enough to illustrate all angles of the problem. We also like to see the expected results given the test data.

This helps to clarify your question, and it makes it easy to copy and paste into a query window to develop a tested solution.

Beware that any solution you get here is likely to be for SQL Server. The appearance of CURRENT_DATE in your query, makes me believe that you are running something else And in that case, it may be better to find a forum dedicated to your product.

Answer 1

Olaf Helper 47,621

Why do you JOIN on column "LoginTimestamp"? If LoginTimestamp changes the JOIN fails and returns the customer again; remove it.

arkiboys 9,711 Reputation points

2023-09-20T08:56:45.8966667+00:00

That is because loginTimestamp is new for each day and each day can have a separate code
LiHongMSFT-4306 31,621 Reputation points

2023-09-21T07:45:09.4066667+00:00

Hi @arkiboys

It is recommended that you provide CREATE TABLE statements and INSERT statements, just the key columns and some sample records, in order to reproduce the issue and test.

Share via

left join to insert

0 additional answers

Your answer