Professional Web Applications Themes

Newbie: JOIN verses additional queries - MySQL

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 ...

  1. #1

    Default 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

    lister Guest

  2. #2

    Default Re: Newbie: JOIN verses additional queries

    >The product table has a MoreInfoID column to tie the two together, but 

    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.
     
     

    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.
     

    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.
     
    Gordon Guest

  3. #3

    Default Re: Newbie: JOIN verses additional queries



    On Jan 27, 6:49 pm, org (Gordon Burditt) wrote: 
    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

    lister Guest

  4. #4

    Default Re: Newbie: JOIN verses additional queries

    >> If you do a normal join, and there is no MOREINFO row with an ID 
    >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?[/ref]

    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.



    Gordon Guest

Similar Threads

  1. SSL on cold fusion verses asp.net
    By drewex in forum Macromedia ColdFusion
    Replies: 2
    Last Post: March 24th, 11:16 PM
  2. 2 SQL queries in 1 asp page -- Newbie
    By Micromanaged in forum ASP Database
    Replies: 3
    Last Post: June 16th, 06:50 PM
  3. A bundle of newbie queries
    By Gawnsoft in forum Ruby
    Replies: 10
    Last Post: August 2nd, 03:37 PM
  4. Replies: 2
    Last Post: July 10th, 11:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139