Flagging in one query what is returned by another

I have two tables.

tblCategories - which holds a list of categories. Fields are CategoryID, CategoryDescription

tblJobCategories which holds a list of categories selected for a specific job. Fields are JobCategoryID(Primary key), CategoryID and JobID

When a user is looking at a new job they can select a categories link which displays a list of categories from tblCategories. They can select one or more of the categories and these are written to tblJobCategories.

When they are editing the job, I want them to click on the Categories link to again display the list of Categories from tblCategories but, I'd like to show the checkboxes next to Categories already selected as being ticked.

So, I have

Code:

---

Select CategoryID, CategoryDescription from tblCategories

---

to get the generic list of categories and

Code:

---

Select CategoryID from tblJobCategories WHERE JobID = @JobID

---

to get the list of Categories for that job.

How can I 'combine' the two statements so I can flag which categories in the generic list already appear in the specific list?

Thanks for any help.

I can't completely understand what you are trying to accomplish, but if you are trying to limit records shown in your first query with the constraint in the second query, you could try:

Code:

---

"SELECT CategoryID, CategoryDescription " & _
"FROM tblCategories INNER JOIN tblJobCategories ON tblCategories.CategoryID = tblJobCategores.CategoryID " & _
"WHERE tblJobCategories.JobID = @JobID;"

---

Code:

---

SELECT CategoryID, CategoryDescription, JobId
FROM tblCategories
Left Join tblJobCategories ON tblCategories.CategoryID = tblJobCategories.CategoryID
            And tblJobCategories.JobID = @JobID

---

All records from tblCategories will be returned. The JobId field will contain Null if there is no match in tblJobCategories otherwise it will equal @JobId.

Thanks very much for that.

Is that what he wanted? I had no idea.. Nice use of left join :thumb: