Can't get query to work - not understanding right

[duanecwilson]

I have this query that has a bunch of customers who have logged in or not. There are only 3 customers who have signed in, but when I make this query as a left join, it puts "YES" for Signed_In_Yet for every one on the left side (over 1000) even though the second query it's based on only returns 3. When I make it a right join, only the 3 records are obtained. I want to return all the records and only put the value "Yes" in the signed_in_yet column or leave it blank if they have not. Ideally, I would like to put "No" in the column, but I don't know if I can do that easily or not. Here is the first query:

SELECT WebAchieveWorkTable.[CSD ID], WebAchieveWorkTable.SRC_CUST_NAME, WebAchieveWorkTable.SRC_CUST_ID, WebAchieveWorkTable.STATUS, WebAchieveWorkTable.[CONTACT NAME], WebAchieveWorkTable.[CONTACT PHONE], qLoggedInYet.LoggedIn AS Signed_On_Yet_YN
FROM WebAchieveWorkTable LEFT JOIN qLoggedInYet ON WebAchieveWorkTable.SRC_CUST_ID = qLoggedInYet.SRC_CUST_ID;

This is the query qLoggedInYet referred to above that returns only the 3 records and puts "Yes" in the LoggedInYet column:

SELECT LoggedInYet.EXISTING_CWI_ID, WebAchieveWorkTable.SRC_CUST_ID, "Yes" AS LoggedIn
FROM LoggedInYet INNER JOIN WebAchieveWorkTable ON LoggedInYet.EXISTING_CWI_ID = WebAchieveWorkTable.EXISTING_CWI_ID;

How do I write that first query again so it shows all the records from the 1st query, but only shows "Yes" in the Signed_In_Yet column for those records that match the second query?

qLoggedInYet is based on just a single column field from the table LoggedInYet that has customer numbers who have logged in yet and I have created a calculated field of "Yes" for each record (only 3 at this time). The first query is joined to the second as that is the only way I can find out those 3 customers. I know I have probably gone about this wrong, but I really could use the help getting my logic right. I don't know why when I do the left join, I get "Yes" in all the 1000 records.

Duane Wilson

 

[PHV]

You may try this:
SELECT W.[CSD ID], W.SRC_CUST_NAME, W.SRC_CUST_ID, W.STATUS, W.[CONTACT NAME], W.[CONTACT PHONE]
, IIf(L.EXISTING_CWI_ID Is Null, "No", "Yes") AS Signed_On_Yet_YN
FROM WebAchieveWorkTable AS W LEFT JOIN LoggedInYet AS L ON W.EXISTING_CWI_ID = L.EXISTING_CWI_ID

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[duanecwilson]

Thank you! That works perfectly. I must have had a mental block or something, but the second query wasn't even necessary; this reply just queries that second table directly, and works great.

I am not sure why my initial query returned "Yes" for all the records - I still must be missing some concept in SQL. If any one can explain that, it may help me in the future.


Duane Wilson