Newbie: JOIN verses additional queries

Hi all,

I have two tables. PRODUCT and MOREINFO

The product table has a MoreInfoID column to tie the two together, but
not every product has more info (probably less than a quarter). In
this case, the MoreInfoID column contains -1

I want to display all products, and any further info associated with
that product. Do I..

A) Do some kind of join on the two tables and accept the redundancy of
lots of MOREINFO columns that won't be used. (ps. What will happen for
rows that have a MoreInfoID of -1? Sorry, I'm really new to this)

B) Query the PRODUCT table, and then query the MOREINFO table for each
of the products that have a valid MoreInfoID?

I'm leaning toward B at the moment, but don't want to do this if it is
hugely inefficient :-?

Can any SQL gurus point me in the right direction.

Many thanks,
Lister

>The product table has a MoreInfoID column to tie the two together, but
>not every product has more info (probably less than a quarter). In
>this case, the MoreInfoID column contains -1

IS there a MOREINFO table row with ID of -1? It would be more
standard practice to set MoreInfoID to null rather than -1 for this
situation.

>I want to display all products, and any further info associated with
>that product. Do I..

>A) Do some kind of join on the two tables and accept the redundancy of
>lots of MOREINFO columns that won't be used. (ps. What will happen for
>rows that have a MoreInfoID of -1? Sorry, I'm really new to this)

If you do a normal join, and there is no MOREINFO row with an ID
of -1, the rows with a MoreInfoID of -1 will not be included (which
seems to be not what you want). If you do a LEFT JOIN ON
PRODUCT.MoreInfoID = MOREINFO.id, then the columns of the MOREINFO
table you request will be returned as null when there isn't a
corresponding row.

>B) Query the PRODUCT table, and then query the MOREINFO table for each
>of the products that have a valid MoreInfoID?

Doing a lot of queries which return a single row is usually less efficient
than doing a query which returns all the data in one query. It's up to
you to tell whether it's "hugely inefficient" for your application.

>I'm leaning toward B at the moment, but don't want to do this if it is
>hugely inefficient :-?

On Jan 27, 6:49 pm, gordonb.t8...@burditt.org (Gordon Burditt) wrote:
> If you do a normal join, and there is no MOREINFO row with an ID
> of -1, the rows with a MoreInfoID of -1 will not be included (which
> seems to be not what you want). If you do a LEFT JOIN ON
> PRODUCT.MoreInfoID = MOREINFO.id, then the columns of the MOREINFO
> table you request will be returned as null when there isn't a
> corresponding row.
I'm with you. Thanks for the advice and concise clarification.

Looks like I'll be doing a join then. Could I just pick your brains
once more on the syntax of the join?
I'm mainly unsure of where to put the WHERE, and also whether it is
possible to pick which columns of the right hand table I want, or if I
have to have the lot?

I've come up with:
SELECT * FROM PRODUCT WHERE PRODUCT.group=whatever LEFT JOIN MOREINFO
ON PRODUCT.MoreInfoID = MOREINFO.id

How do I specify particular right hand columns with the above?

Many thanks for your help,
Lister

>> If you do a normal join, and there is no MOREINFO row with an ID
>> of -1, the rows with a MoreInfoID of -1 will not be included (which
>> seems to be not what you want). If you do a LEFT JOIN ON
>> PRODUCT.MoreInfoID = MOREINFO.id, then the columns of the MOREINFO
>> table you request will be returned as null when there isn't a
>> corresponding row.
>I'm with you. Thanks for the advice and concise clarification.
>
>Looks like I'll be doing a join then. Could I just pick your brains
>once more on the syntax of the join?
>I'm mainly unsure of where to put the WHERE, and also whether it is
>possible to pick which columns of the right hand table I want, or if I
>have to have the lot?
>
>I've come up with:
>SELECT * FROM PRODUCT WHERE PRODUCT.group=whatever LEFT JOIN MOREINFO
>ON PRODUCT.MoreInfoID = MOREINFO.id
>
>How do I specify particular right hand columns with the above?

You probably want something like:

SELECT PRODUCT.*, MOREINFO.id, MOREINFO.field2 FROM PRODUCT
LEFT JOIN MOREINFO ON PRODUCT.MoreInfoID = MOREINFO.id
WHERE PRODUCT.group = whatever;

Note that you probably want to qualify every column reference
(including *) with a table name, although it might accept it if
there is only one table with that column name. References in the
WHERE clause to columns in the LEFT JOINed table where there is no
matching record have the value null.

You can also give a table name an alias, and use the alias to qualify
fields. This allows you to join a table with itself, and refer
separately to different instances of the table. For instance, you
might have:

SELECT P.id, C.id, P.firstname, P.lastname, C.firstname, C.lastname
FROM PEOPLE P
LEFT JOIN PEOPLE C ON C.mother_id = P.id
WHERE C.birthyear < P.birthyear + 8

Here, P refers to the parent (mother) and C refers to the child.
The results might use the same record once as a child and several
times as a parent.

This query looks for mothers who gave birth at age 8 or less, and
includes cases where the mother gave birth before her own birth.
Subtracting birthyears is a crude approximation for age, but sometimes
it's all you've got. In genealogy databases, this probably indicates
a mistake involving similarly-named people in different generations.