quote:

---

Originally posted by rmiao

Then you are better to read about join in books online.

---

quote:

---

Originally posted by jtrumbul

Hello All,

I have what i believe to be a pretty basic SQL question. I have 2 tables, One called participant, and another called cnAvailableParticipant_List they are related with a field called userid I would like to select the firstname, lastname, userid, fields from the participant table and the dnnuserid field from the cnAvailableParticipant_List table where the users have a record in the participant table but do NOT have a record in the other table. Basicially i want to select all the users who are essentially not checked in. So far i have:

SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID FROM Participant p, cnAvailableParticipant_List ap
WHERE
ORDER BY LName, FName

can someone explain to me what i need in the where clause and why. I don't just want the answer, i would like to know why it works the way it does as well. Sorry for the basic question but im new to SQL

Thanks all help is appreciated,
John

---

SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID 
FROM Participant p
LEFT JOIN cnAvailableParticipant_List ap
ON ap.LinkingColumn=p.LinkingColumn
WHERE ap.LinkingColumn IS NULL
ORDER BY p.LName, p.FName

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by jtrumbul

Hello All,

I have what i believe to be a pretty basic SQL question. I have 2 tables, One called participant, and another called cnAvailableParticipant_List they are related with a field called userid I would like to select the firstname, lastname, userid, fields from the participant table and the dnnuserid field from the cnAvailableParticipant_List table where the users have a record in the participant table but do NOT have a record in the other table. Basicially i want to select all the users who are essentially not checked in. So far i have:

SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID FROM Participant p, cnAvailableParticipant_List ap
WHERE
ORDER BY LName, FName

can someone explain to me what i need in the where clause and why. I don't just want the answer, i would like to know why it works the way it does as well. Sorry for the basic question but im new to SQL

Thanks all help is appreciated,
John

---

SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID 
FROM Participant p
LEFT JOIN cnAvailableParticipant_List ap
ON ap.LinkingColumn=p.LinkingColumn
WHERE ap.LinkingColumn IS NULL
ORDER BY p.LName, p.FName

However i didnt understand the purpose of second part. What is the purpose of returning the value of dnnuserid field from the cnAvailableParticipant_List table when you're looking at records having no matching value in cnAvailableParticipant_List table?the value returned for that field will always be NULL

---

quote:

---

Originally posted by jtrumbul

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by jtrumbul

Hello All,

I have what i believe to be a pretty basic SQL question. I have 2 tables, One called participant, and another called cnAvailableParticipant_List they are related with a field called userid I would like to select the firstname, lastname, userid, fields from the participant table and the dnnuserid field from the cnAvailableParticipant_List table where the users have a record in the participant table but do NOT have a record in the other table. Basicially i want to select all the users who are essentially not checked in. So far i have:

SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID FROM Participant p, cnAvailableParticipant_List ap
WHERE
ORDER BY LName, FName

can someone explain to me what i need in the where clause and why. I don't just want the answer, i would like to know why it works the way it does as well. Sorry for the basic question but im new to SQL

Thanks all help is appreciated,
John

---

SELECT p.ParticipantID as ID, p.FName as FName, p.LName as LName, ap.DNNID 
FROM Participant p
LEFT JOIN cnAvailableParticipant_List ap
ON ap.LinkingColumn=p.LinkingColumn
WHERE ap.LinkingColumn IS NULL
ORDER BY p.LName, p.FName

However i didnt understand the purpose of second part. What is the purpose of returning the value of dnnuserid field from the cnAvailableParticipant_List table when you're looking at records having no matching value in cnAvailableParticipant_List table?the value returned for that field will always be NULL

---

It worked like a charm. You are correct in your question, what i meant to write was p.UserID as DNNID not select it from the ap.table. Sorry it was late and i was tired as all hell. Now with this query reading it still confuses me a bit. when you say where ap.linking column is null makes me think it would select records that exists in both tables only where that column from the ap table is null? i see it is working properly but if you get a second do you think you can break down the query for me as to why? im sure its annoying but as i stated above im as interested in why the answer works as opposed to just getting it working.

thanks for all the help i appreciate it much.
-John

---