pull only records that have null in a particular field

[myatia]

Hi, all,

In the query below, I'm trying to find the records in table A that don't have a match in table B (i.e., the ssid field is null).

SELECT A.code1, A.ses, A.yr, B.ssid ;
FROM A LEFT JOIN B ;
ON (A.id = B.id AND A.code1 = B.code1 AND A.yr = B.yr) ;
WHERE A.id = 74283 AND B.ssid IS NULL

When I remove "B.ssid IS NULL", null values appear for ssid in some of the results. However, when I add "B.ssid IS NULL", no results are returned. Does anyone know what gives?

Thanks!

Misty

 

[amorous]

Try this:

When you know B.ssid is NULL then i dont think you need it in the SELECT..

SELECT A.code1, A.ses, A.yr;
FROM A LEFT JOIN B ;
ON (A.id = B.id AND A.code1 = B.code1 AND A.yr = B.yr) ;
WHERE A.id = 74283 AND ISNULL(B.ssid)

-VJ

 

[myatia]

Hi, VJ,

I took out ssid and tried both "is null" and "IsNull()", but it still doesn't return any results. Thanks for trying, though. Do you have any other advice?

Misty

 

[amorous]

Can you show your table fields and some data and the kind of result you are looking for.

-VJ

 

[johnherman]

select ssid from table A where ssid not in (select distinct(ssid) from table B)

Sometimes the grass is greener on the other side because there is more manure there - original.

 

[dinzana]

did you try

where ltrim(rtrim(ssid)) is null ?

it's worth a shot.

SELECT A.code1, A.ses, A.yr, B.ssid
FROM A LEFT JOIN B
ON (A.id = B.id AND A.code1 = B.code1 AND A.yr = B.yr)
WHERE A.id = 74283 AND ltrim(rtrim(B.ssid)) IS NULL


-dinzana

 

[myatia]

Sure. I have two tables. ConferenceSpeakers lists conference speakers and information on the sessions they're going to speak at. A speaker can speak at multiple sessions, so she might have multiple records in the table. Additionally, a session can have multiple speakers. The relevant fields in ConferenceSpeakers are:

code1 = conference code (this with yr uniquely identifies a conference
yr = year of the conference
ses = session code
id = speaker id

The second table is SpeakerSignup. When a speaker adds information about their session (title, description, objectives, etc.) on our web site, they get a record in the Speaker Signup table. The information isn't saved to this table, though. The only info in the SpeakerSignup table is the following:

ssid = unique id number
code1 = conference code
yr = year of the conference
ses = session code
id = speaker id
completed = datestamp of when the process was completed

I want to be able to find out which session-speaker pairs don't have any details yet.

Here's the [non-working] code with the actual table names:

SELECT ConferenceSpeakers.code1 

FROM ConferenceSpeakers LEFT JOIN SpeakerSignup ON (ConferenceSpeakers.id = SpeakerSignup.id AND ConferenceSpeakers.code1 = SpeakerSignup.code1 AND ConferenceSpeakers.yr = SpeakerSignup.yr AND ConferenceSpeakers.ses = SpeakerSignup.ses) 

WHERE ConferenceSpeakers.id = 2225 AND ssid IS NULL

Ideally, I would just have an "InfoAdded" bool field in the ConferenceSpeakers table. However, the data from that table is coming from a daily export, so I need to hold the info in a separate table so it isn't overwritten every day.

Let me know if this helps or if you have any questions. Thanks much!

Misty

 

[amorous]

Try this:

SELECT A.code1, A.ses, A.yr;
FROM A LEFT JOIN B ;
ON (A.id = B.id AND A.code1 = B.code1 AND A.yr = B.yr) ;
WHERE A.id = 74283 AND (A.ssid<>B.ssid)

-VJ