SQL Query, two tables but also 0 values!

[sanchez_1960]

I'm doing something like this:

SELECT * from people, jobs
WHERE
jobs.People_ID = people.People_ID

Thats all ok, except I want to show results where the jobs table doesn't have an associate ID in people, so technially it hasn't been assigned a person yet.

My solution to the problem was to simply list the jobs table on its own and do a secondary SQL on each printed row to look up the the people name.

Tho the overhead on this will obviously be complete un-necessary.

All I need to do is rewrite my main query so it also shows values where the two don't match up.

[bubbisthedog]

Some friendly advice :) :

a) Next time consider being much less verbose, listing your table structures with some sample data, as well as your actual results v. desired results in table format.

b) It gets very confusing when you refer to names like "associate ID," which (I think) is actually referring to "People_ID," the actual field name. Refer only to field names, for those are what're going to be used in the actual query.

c) Consider using the JOIN/ON syntax, rather putting multiple tables in the FROM clause. It's obviously up to you, but I'd at least consider it.

d) Let us know what product and version you're using (MySQL 5.0, SQL Server 2005, etc.). Syntax can vary from product to product.

The following is all I can offer based on your request, until, that is, a) you revise your post or b) someone else understands what you're asking better than I.

Your statement can be converted to the following:

select *
from people
join jobs
on jobs.People_ID = people.People_ID

This query will return only results where the People_IDs exist in both tables. Therefore, I don't see what the problem is if the 'associate ID' equals 'People_ID.'