Pretty basic SQL question (SOLVED)

whuppins

EDIT: See the bottom of the post; I may have stumbled onto the answer right after I posted.

OK, I suck at SQL, so layman's terms for the most part, if you please. Here's the problem in its simplest form:

I want to query a database with two tables that consist of the following data:

ITEMS

---

ITEM_NUMBER
NAME
STATUS

EXTRA_INFO

---

ITEM_NUMBER
INFO

The ITEMS table is the 'master list' of all items on record. It contains a NAME field and a STATUS field that will be the criteria I'm querying by.

The EXTRA_INFO table contains optional extra data that pertains to some of the items. Not every item has an entry in EXTRA_INFO, only the ones that have INFO to record. Entries are created in EXTRA_INFO as needed, with the ITEM_NUMBER field to link it back to the main table.

I'm trying to write a query that returns the names of all items with status "A", along with any INFO associated with that item. I started out with this query:

[html]
select items.name,
extra_info.info
from items,
extra_info
where ( items.status = 'A' ) and
( extra_info.item_number = items.item_number )
[/html]

This fails to return any item that doesn't have an entry in EXTRA_INFO. So, I thought I'd be clever and add in a plus sign, which is something I'd done in the past whenever I didn't want to limit my results to only those that had a link to another table referenced in the Where clause:

[html]
select items.name,
extra_info.info
from items,
extra_info
where ( items.status = 'A' ) and
( extra_info.item_number = items.item_number (+) )
[/html]

Apparently, the plus sign doesn't work the way I thought it did, because this didn't seem to make a difference. I'm still getting only the items whose item numbers appear in EXTRA_INFO, not simply all items with the "A" status.

At this point, I think I can use a union to get what I need (though I'm not exactly sure how), but I'd like to know if there's a way to solve this without doing so. I'm trying to keep this syntax as simple and efficient as possible, and I'm under the impression that you really shouldn't use a union unless you have to. Is there a way to do this without a union? If not, what should the query look like in the example above?

Thanks.

(P.S. - If you're wondering why I used HTML tags instead of CODE, it's because CODE doesn't preserve spacing perfectly. Just bear with me.)

EDIT: I just tried it again, flipping the order of the last line (to "( items.item_number = extra_info.item_number (+) )") and it seems to work... was this all I needed to do?

Lewisham

Yeah, you did it, because you're making an outer join. What you're saying is that if there isn't an entry in extra_info, that's OK, just join it to a null value instead. When you had it the other way around, you were asking for the inverse, which didn't work.

whuppins

Lewisham wrote: »

Yeah, you did it, because you're making an outer join. What you're saying is that if there isn't an entry in extra_info, that's OK, just join it to a null value instead. When you had it the other way around, you were asking for the inverse, which didn't work.

OK, excellent. That kind of dawned on me (only without all the cool "inner join"/"outer join" terminology) as I looked at it again.

Thanks!