Professional Web Applications Themes

Sql Left Join Help - MySQL

I will be the first to admit that my SQL knowledge is very limited. However, I would like some help with a query example. I have four tables (A, B, C, D) that all have an index and/or keyfield (let's call it ID). In table A, ID is the key. In table B, ID is the key. In table C, ID is an index (with multiple entries). In table D, ID is an index (with multiple entries). I need to be able to join all the tables together, but I want to limit the entries in the resulting Row Set ...

  1. #1

    Default Sql Left Join Help

    I will be the first to admit that my SQL knowledge is very limited.
    However, I would like some help with a query example. I have four
    tables (A, B, C, D) that all have an index and/or keyfield (let's call
    it ID). In table A, ID is the key. In table B, ID is the key. In
    table C, ID is an index (with multiple entries). In table D, ID is an
    index (with multiple entries). I need to be able to join all the
    tables together, but I want to limit the entries in the resulting Row
    Set to be unique for each duplicate entry in tables C and D.
    Currently, if I have 14 entries in table C and 5 entries in table D,
    then the Row Set would produce 70 rows returned. I want this to return
    14 rows with the last 11 rows not having any values for the entries not
    in table D.

    Can anyone help?

    Marc.Farrow Guest

  2. #2

    Default Re: Sql Left Join Help

    Marc.Farrow wrote: 

    Descriptions like this aren't real helpful.

    How about posting the schema and some sample data so we can better see
    what you're getting at.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Sql Left Join Help

    Table A

    ID Name City
    1 Test Somewhere


    Table B

    ID Type Dept
    1 EMP Sales

    Table C
    ID ProductCode
    1 A111
    1 A2222
    1 B33
    1 C45
    1 A44
    1 A23
    1 AB
    1 B376
    1 C49347
    1 A1297
    1 A9975
    1 B3457
    1 C49786
    1 A3975
    1 A2385
    1 B0857
    1 C8684

    Table D
    ID Location
    1 CA
    1 GA
    1 SC

    I want to select the IDs from all 4 tables where ID = 1, but I do want
    a row set in the follownig format:

    ID Name City Dept ProductCode Type Location
    1 Test Somewhere Sales A11 EMP CA
    1 Test Somewhere Sales A11 EMP SC
    1 Test Somewhere Sales A11 EMP GA
    1 Test Somewhere Sales A2222 EMP CA
    1 Test Somewhere Sales A2222 EMP GA
    1 Test Somewhere Sales A2222 EMP SC

    But rather:

    ID Name City Dept ProductCode Type Location
    1 Test Somewhere Sales A11 EMP CA
    1 Test Somewhere Sales A11 EMP SC
    1 Test Somewhere Sales A11 EMP GA
    1 Test Somewhere Sales B3457 EMP
    1 Test Somewhere Sales C49786 EMP
    1 Test Somewhere Sales A3975 EMP
    1 Test Somewhere Sales A2385 EMP




    Jerry Stuckle wrote: 
    >
    > Descriptions like this aren't real helpful.
    >
    > How about posting the schema and some sample data so we can better see
    > what you're getting at.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Marc.Farrow Guest

  4. #4

    Default Re: Sql Left Join Help

    Marc.Farrow wrote: 
    >>
    >>Descriptions like this aren't real helpful.
    >>
    >>How about posting the schema and some sample data so we can better see
    >>what you're getting at.
    >>[/ref]
    > Table A
    >
    > ID Name City
    > 1 Test Somewhere
    >
    >
    > Table B
    >
    > ID Type Dept
    > 1 EMP Sales
    >
    > Table C
    > ID ProductCode
    > 1 A111
    > 1 A2222
    > 1 B33
    > 1 C45
    > 1 A44
    > 1 A23
    > 1 AB
    > 1 B376
    > 1 C49347
    > 1 A1297
    > 1 A9975
    > 1 B3457
    > 1 C49786
    > 1 A3975
    > 1 A2385
    > 1 B0857
    > 1 C8684
    >
    > Table D
    > ID Location
    > 1 CA
    > 1 GA
    > 1 SC
    >
    > I want to select the IDs from all 4 tables where ID = 1, but I do want
    > a row set in the follownig format:
    >
    > ID Name City Dept ProductCode Type Location
    > 1 Test Somewhere Sales A11 EMP CA
    > 1 Test Somewhere Sales A11 EMP SC
    > 1 Test Somewhere Sales A11 EMP GA
    > 1 Test Somewhere Sales A2222 EMP CA
    > 1 Test Somewhere Sales A2222 EMP GA
    > 1 Test Somewhere Sales A2222 EMP SC
    >
    > But rather:
    >
    > ID Name City Dept ProductCode Type Location
    > 1 Test Somewhere Sales A11 EMP CA
    > 1 Test Somewhere Sales A11 EMP SC
    > 1 Test Somewhere Sales A11 EMP GA
    > 1 Test Somewhere Sales B3457 EMP
    > 1 Test Somewhere Sales C49786 EMP
    > 1 Test Somewhere Sales A3975 EMP
    > 1 Test Somewhere Sales A2385 EMP
    >
    >
    >
    >[/ref]

    (Top posting fixed)

    I guess I don't see where that's possible. I don't see where there is a
    linkage between, for instance, ProductCode and Location except they both
    have an ID of 1.

    I'm not sure what the real problem you're trying to solve is, but it
    looks like you need to rethink your schema.

    BTW - please don't top post. Thanks.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  5. #5

    Default Re: Sql Left Join Help


    Jerry Stuckle wrote: 
    > > Table A
    > >
    > > ID Name City
    > > 1 Test Somewhere
    > >
    > >
    > > Table B
    > >
    > > ID Type Dept
    > > 1 EMP Sales
    > >
    > > Table C
    > > ID ProductCode
    > > 1 A111
    > > 1 A2222
    > > 1 B33
    > > 1 C45
    > > 1 A44
    > > 1 A23
    > > 1 AB
    > > 1 B376
    > > 1 C49347
    > > 1 A1297
    > > 1 A9975
    > > 1 B3457
    > > 1 C49786
    > > 1 A3975
    > > 1 A2385
    > > 1 B0857
    > > 1 C8684
    > >
    > > Table D
    > > ID Location
    > > 1 CA
    > > 1 GA
    > > 1 SC
    > >
    > > I want to select the IDs from all 4 tables where ID = 1, but I do want
    > > a row set in the follownig format:
    > >
    > > ID Name City Dept ProductCode Type Location
    > > 1 Test Somewhere Sales A11 EMP CA
    > > 1 Test Somewhere Sales A11 EMP SC
    > > 1 Test Somewhere Sales A11 EMP GA
    > > 1 Test Somewhere Sales A2222 EMP CA
    > > 1 Test Somewhere Sales A2222 EMP GA
    > > 1 Test Somewhere Sales A2222 EMP SC
    > >
    > > But rather:
    > >
    > > ID Name City Dept ProductCode Type Location
    > > 1 Test Somewhere Sales A11 EMP CA
    > > 1 Test Somewhere Sales A11 EMP SC
    > > 1 Test Somewhere Sales A11 EMP GA
    > > 1 Test Somewhere Sales B3457 EMP
    > > 1 Test Somewhere Sales C49786 EMP
    > > 1 Test Somewhere Sales A3975 EMP
    > > 1 Test Somewhere Sales A2385 EMP
    > >
    > >
    > >
    > >[/ref]
    >
    > (Top posting fixed)
    >
    > I guess I don't see where that's possible. I don't see where there is a
    > linkage between, for instance, ProductCode and Location except they both
    > have an ID of 1.
    >
    > I'm not sure what the real problem you're trying to solve is, but it
    > looks like you need to rethink your schema.
    >
    > BTW - please don't top post. Thanks.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    TOP POST??? What is that?

    Marc.Farrow Guest

  6. #6

    Default Re: Sql Left Join Help

    Marc.Farrow wrote: 
    >> 
    >>
    >>(Top posting fixed)
    >>
    >>I guess I don't see where that's possible. I don't see where there is a
    >>linkage between, for instance, ProductCode and Location except they both
    >>have an ID of 1.
    >>
    >>I'm not sure what the real problem you're trying to solve is, but it
    >>looks like you need to rethink your schema.
    >>
    >>BTW - please don't top post. Thanks.
    >>[/ref]
    >
    >
    > TOP POST??? What is that?
    >[/ref]

    It's posting your response at the top of the message, instead of the bottom.

    This newsgroup uses bottom posting (like you just did - great!), or
    intermeshed posting (where you put responses immediately after
    questions) as a standard.

    Thanks.


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Sql Left Join Help


    Jerry Stuckle wrote: 
    > >
    > >
    > > TOP POST??? What is that?
    > >[/ref]
    >
    > It's posting your response at the top of the message, instead of the bottom.
    >
    > This newsgroup uses bottom posting (like you just did - great!), or
    > intermeshed posting (where you put responses immediately after
    > questions) as a standard.
    >
    > Thanks.
    >
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================[/ref]

    Well that is just a silly rule.

    Marc.Farrow Guest

  8. #8

    Default Re: Sql Left Join Help

    Marc.Farrow wrote: 
    >>
    >>It's posting your response at the top of the message, instead of the bottom.
    >>
    >>This newsgroup uses bottom posting (like you just did - great!), or
    >>intermeshed posting (where you put responses immediately after
    >>questions) as a standard.
    >>
    >>Thanks.
    >>
    >>[/ref]
    >
    >
    > Well that is just a silly rule.
    >[/ref]


    Not really. Then people can read the messages from the top down,
    instead of having to go down and back up to read the question then the
    response.

    Not everyone is using a web interface. In fact, most serious usenet
    readers use a newsreader.

    A. Because it upsets the natural reading order
    Q. Why is it so bad?
    A. Top posting
    Q. What is the most annoying thing on usenet?

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: Sql Left Join Help

    Marc.Farrow wrote: 
    >
    > Well that is just a silly rule.[/ref]

    It's not a rule; it's a convention.

    The value of a convention is achieved when everyone follows it, and if
    some people depart from the convention, the value is spoiled.

    For example, if some people top-post and some people bottom-post, that's
    the worst of all cases. It makes it very awkward to follow the thread
    of discussion in interpolated (indented) article content. So it's best
    if everyone follows the same convention.

    The great majority of people on Usenet prefer bottom-posting, for the
    reason that Jerry illustrated in the "A:/Q:" example. So let's all
    follow the convention of bottom-posting, and life will be grand.

    Regards,
    Bill K.
    Bill Guest

Similar Threads

  1. Is left-join faster then inner join?
    By howachen@gmail.com in forum MySQL
    Replies: 5
    Last Post: March 5th, 07:11 AM
  2. left join limit 1
    By in forum MySQL
    Replies: 7
    Last Post: March 27th, 04:38 AM
  3. Left join isn't joining
    By Bill in forum MySQL
    Replies: 7
    Last Post: December 23rd, 04:47 PM
  4. left join problem
    By AK in forum IBM DB2
    Replies: 8
    Last Post: August 8th, 12:16 PM
  5. Select Left Join AND Right Join
    By David in forum Microsoft SQL / MS SQL Server
    Replies: 1
    Last Post: July 15th, 03:42 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