Professional Web Applications Themes

left join limit 1 - MySQL

I have 3 tables, one main table, and two tables that reference the first table. the two reference tables may have more than one entry to the first one. I am able to use left joins to see all the data for the three tables, however, for a specific purpose, i only want to see the first instance of the 2 joined tables. Is there a way to do SELECT DISTINCT, but only have it distinct on the columns from the first table, or do a subquery to get the other two tables, using LIMIT 1. I'm using mysql 3.26. ...

  1. #1

    Default left join limit 1

    I have 3 tables, one main table, and two tables that reference the first
    table. the two reference tables may have more than one entry to the first
    one. I am able to use left joins to see all the data for the three tables,
    however, for a specific purpose, i only want to see the first instance of
    the 2 joined tables. Is there a way to do SELECT DISTINCT, but only have it
    distinct on the columns from the first table, or do a subquery to get the
    other two tables, using LIMIT 1. I'm using mysql 3.26. If this limits me
    in any way, let me know what i can do w/ 4.1 or 5.0. thank you.


    Guest

  2. #2

    Default Re: left join limit 1

    Anybody? Bueller? Bueller?


    <a> wrote in message news:TfWdnWlQENNvhr_ZnZ2dnUVZ_uudnZ2dcomcast.com. ..
    >I have 3 tables, one main table, and two tables that reference the first
    >table. the two reference tables may have more than one entry to the first
    >one. I am able to use left joins to see all the data for the three tables,
    >however, for a specific purpose, i only want to see the first instance of
    >the 2 joined tables. Is there a way to do SELECT DISTINCT, but only have
    >it distinct on the columns from the first table, or do a subquery to get
    >the other two tables, using LIMIT 1. I'm using mysql 3.26. If this limits
    >me in any way, let me know what i can do w/ 4.1 or 5.0. thank you.
    >

    Guest

  3. #3

    Default Re: left join limit 1

    <a> wrote in message news:TOSdncbGgoXrWLnZnZ2dnUVZ_s-dnZ2dcomcast.com...
    >>I have 3 tables, one main table, and two tables that reference the first
    >>table. the two reference tables may have more than one entry to the first
    >>one. I am able to use left joins to see all the data for the three
    >>tables, however, for a specific purpose, i only want to see the first
    >>instance of the 2 joined tables. Is there a way to do SELECT DISTINCT,
    >>but only have it distinct on the columns from the first table, or do a
    >>subquery to get the other two tables, using LIMIT 1. I'm using mysql
    >>3.26. If this limits me in any way, let me know what i can do w/ 4.1 or
    >>5.0. thank you.
    When asking questions like this, it's helpful to describe the schema and
    also specify details like which field you use for the criteria of "first
    instance", etc.

    I'll invent a sample schema:

    CREATE TABLE A (
    A_ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    );

    CREATE TABLE B (
    A_ID INTEGER NOT NULL,
    CREATED_DATE DATE NOT NULL,
    FOREIGN KEY (A_ID) REFERENCES A(A_ID)
    );

    CREATE TABLE C (
    A_ID INTEGER NOT NULL,
    CREATED_DATE DATE NOT NULL,
    FOREIGN KEY (A_ID) REFERENCES A(A_ID)
    );

    The following query should work in any version of MySQL:

    SELECT A.*, B1.*, C1.*
    FROM A
    LEFT OUTER JOIN B AS B1 ON A.A_ID = B1.A_ID
    LEFT OUTER JOIN B AS B2 ON A.A_ID = B2.A_ID
    AND B1.CREATED_DATE > B2.CREATED_DATE
    LEFT OUTER JOIN C AS C1 ON A.A_ID = C1.A_ID
    LEFT OUTER JOIN C AS C2 ON A.A_ID = C2.A_ID
    AND C1.CREATED_DATE > C2.CREATED_DATE
    WHERE B2.A_ID IS NULL AND C2.A_ID IS NULL;

    Regards,
    Bill K.


    Bill Karwin Guest

  4. #4

    Default Re: left join limit 1


    "Bill Karwin" <billkarwin.com> wrote in message
    news:e02ht50lrlenews3.newsguy.com...
    > <a> wrote in message news:TOSdncbGgoXrWLnZnZ2dnUVZ_s-dnZ2dcomcast.com...
    >>>I have 3 tables, one main table, and two tables that reference the first
    >>>table. the two reference tables may have more than one entry to the
    >>>first one. I am able to use left joins to see all the data for the three
    >>>tables, however, for a specific purpose, i only want to see the first
    >>>instance of the 2 joined tables. Is there a way to do SELECT DISTINCT,
    >>>but only have it distinct on the columns from the first table, or do a
    >>>subquery to get the other two tables, using LIMIT 1. I'm using mysql
    >>>3.26. If this limits me in any way, let me know what i can do w/ 4.1 or
    >>>5.0. thank you.
    >
    > When asking questions like this, it's helpful to describe the schema and
    > also specify details like which field you use for the criteria of "first
    > instance", etc.
    >
    > I'll invent a sample schema:
    >
    > CREATE TABLE A (
    > A_ID INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
    > );
    >
    > CREATE TABLE B (
    > A_ID INTEGER NOT NULL,
    > CREATED_DATE DATE NOT NULL,
    > FOREIGN KEY (A_ID) REFERENCES A(A_ID)
    > );
    >
    > CREATE TABLE C (
    > A_ID INTEGER NOT NULL,
    > CREATED_DATE DATE NOT NULL,
    > FOREIGN KEY (A_ID) REFERENCES A(A_ID)
    > );
    >
    > The following query should work in any version of MySQL:
    >
    > SELECT A.*, B1.*, C1.*
    > FROM A
    > LEFT OUTER JOIN B AS B1 ON A.A_ID = B1.A_ID
    > LEFT OUTER JOIN B AS B2 ON A.A_ID = B2.A_ID
    > AND B1.CREATED_DATE > B2.CREATED_DATE
    > LEFT OUTER JOIN C AS C1 ON A.A_ID = C1.A_ID
    > LEFT OUTER JOIN C AS C2 ON A.A_ID = C2.A_ID
    > AND C1.CREATED_DATE > C2.CREATED_DATE
    > WHERE B2.A_ID IS NULL AND C2.A_ID IS NULL;
    >
    Thank your for your reply. Let me clarify my work a bit. I have 3 tables.

    Company: company_id, company_name
    Person: person_id, company_id, person_name
    Location: location_id, company_id, location_name

    sometimes there is no person or location entry and sometimes tehre are
    person/lcoation entries per company.

    I use a "SELECT DISTINCT company.company_name, person._person_name,
    location.location_name
    LEFT JOIN person ON company.company_id = person.company_id
    LEFT JOIN location ON company.company_id = location .company_id
    ORDER BY company.compay_name"

    This gives me all the data I want, however it gives me too much, when there
    are two entries under person, or location w/ the same company_id. For my
    query, i just want to know, one person and location entry per company,
    basically, only row per company entry.

    Does your example work in this case?


    Guest

  5. #5

    Default Re: left join limit 1

    <a> wrote in message news:cfmdnebNfKkX_LjZRVn-hgcomcast.com...
    > Thank your for your reply. Let me clarify my work a bit. I have 3
    > tables.
    >
    > Company: company_id, company_name
    > Person: person_id, company_id, person_name
    > Location: location_id, company_id, location_name
    >
    > sometimes there is no person or location entry and sometimes tehre are
    > person/lcoation entries per company.
    >
    > I use a "SELECT DISTINCT company.company_name, person._person_name,
    > location.location_name
    > LEFT JOIN person ON company.company_id = person.company_id
    > LEFT JOIN location ON company.company_id = location .company_id
    > ORDER BY company.compay_name"
    >
    > This gives me all the data I want, however it gives me too much, when
    > there are two entries under person, or location w/ the same company_id.
    > For my query, i just want to know, one person and location entry per
    > company, basically, only row per company entry.
    >
    > Does your example work in this case?
    Probably the solution I gave is applicable.

    But you must specify which one person and which one company do you want to
    display.
    You mentioned "first instance," but how do you measure that? Which one is
    "first?"
    Alphabetically by person_name/location_name? The one with most matching
    entries? Numerically by person_id/location_id?

    Regards,
    Bill K.


    Bill Karwin Guest

  6. #6

    Default Re: left join limit 1


    "Bill Karwin" <billkarwin.com> wrote in message
    news:e045b2017clenews2.newsguy.com...
    > <a> wrote in message news:cfmdnebNfKkX_LjZRVn-hgcomcast.com...
    >> Thank your for your reply. Let me clarify my work a bit. I have 3
    >> tables.
    >>
    >> Company: company_id, company_name
    >> Person: person_id, company_id, person_name
    >> Location: location_id, company_id, location_name
    >>
    >> sometimes there is no person or location entry and sometimes tehre are
    >> person/lcoation entries per company.
    >>
    >> I use a "SELECT DISTINCT company.company_name, person._person_name,
    >> location.location_name
    >> LEFT JOIN person ON company.company_id = person.company_id
    >> LEFT JOIN location ON company.company_id = location .company_id
    >> ORDER BY company.compay_name"
    >>
    >> This gives me all the data I want, however it gives me too much, when
    >> there are two entries under person, or location w/ the same company_id.
    >> For my query, i just want to know, one person and location entry per
    >> company, basically, only row per company entry.
    >>
    >> Does your example work in this case?
    >
    > Probably the solution I gave is applicable.
    >
    > But you must specify which one person and which one company do you want to
    > display.
    > You mentioned "first instance," but how do you measure that? Which one is
    > "first?"
    > Alphabetically by person_name/location_name? The one with most matching
    > entries? Numerically by person_id/location_id?
    It doesn't matter which one. I'm thinking if you didn't specify, it would
    pick the first one entered.


    Guest

  7. #7

    Default Re: left join limit 1

    <a> wrote in message news:jpadncqWGMmnR7jZRVn-sQcomcast.com...
    >> But you must specify which one person and which one company do you want
    >> to display.
    >
    > It doesn't matter which one. I'm thinking if you didn't specify, it would
    > pick the first one entered.
    That is not an assumption you can make in relational databases. Unless you
    specify an order, the rows are returned in some implementation-dependant
    manner. This may vary by the storage engine (MyISAM vs. InnoDB), the version
    (4.0, 4.1, 5.0, etc.), the order in which the records were created or
    modified, or some other factors. Some query features (e.g. DISTINCT, GROUP
    BY, etc.) may have side-effects of applying an order to the rows.

    For the solution query I gave a few messages back, there must be some field
    in your people and locaiton tables by which you can determine which row is
    first, relative to the others. I.e. a field which you would ordinarily use
    in ORDER BY.

    Okay, so if it really doesn't matter which people and location rows you get
    in the query, here's
    a different solution that uses GROUP BY.

    SELECT c.company_name, p.person_name, l.location_name
    FROM company AS c
    LEFT OUTER JOIN person AS p on c.company_id = p.company_id
    LEFT OUTER JOIN location AS l on c.company_id = l.company_id
    GROUP BY c.company_id

    In MySQL, this will return one row for each distinct company, and with the
    company name, it will return one of the matching person and location
    entries, even if multiple persons and locations match the company. The
    values for person and location returned on that row will be unpredictable,
    dependant on some of the factors I described above.

    Note that this behavior of GROUP BY is particular to MySQL; other RDBMS
    brands may treat the query as illegal, since the query results are
    ambiguous.

    Regards,
    Bill K.


    Bill Karwin Guest

  8. #8

    Default Re: left join limit 1


    "Bill Karwin" <billkarwin.com> wrote in message
    news:e04ro409hgenews3.newsguy.com...
    > <a> wrote in message news:jpadncqWGMmnR7jZRVn-sQcomcast.com...
    >>> But you must specify which one person and which one company do you want
    >>> to display.
    >>
    >> It doesn't matter which one. I'm thinking if you didn't specify, it
    >> would pick the first one entered.
    >
    > That is not an assumption you can make in relational databases. Unless
    > you specify an order, the rows are returned in some
    > implementation-dependant manner. This may vary by the storage engine
    > (MyISAM vs. InnoDB), the version (4.0, 4.1, 5.0, etc.), the order in which
    > the records were created or modified, or some other factors. Some query
    > features (e.g. DISTINCT, GROUP BY, etc.) may have side-effects of applying
    > an order to the rows.
    >
    > For the solution query I gave a few messages back, there must be some
    > field in your people and locaiton tables by which you can determine which
    > row is first, relative to the others. I.e. a field which you would
    > ordinarily use in ORDER BY.
    >
    > Okay, so if it really doesn't matter which people and location rows you
    > get in the query, here's
    > a different solution that uses GROUP BY.
    >
    > SELECT c.company_name, p.person_name, l.location_name
    > FROM company AS c
    > LEFT OUTER JOIN person AS p on c.company_id = p.company_id
    > LEFT OUTER JOIN location AS l on c.company_id = l.company_id
    > GROUP BY c.company_id
    >
    > In MySQL, this will return one row for each distinct company, and with the
    > company name, it will return one of the matching person and location
    > entries, even if multiple persons and locations match the company. The
    > values for person and location returned on that row will be unpredictable,
    > dependant on some of the factors I described above.
    >
    > Note that this behavior of GROUP BY is particular to MySQL; other RDBMS
    > brands may treat the query as illegal, since the query results are
    > ambiguous.
    >
    Ah, it worked. the GROUP BY is exactly what i was looking for. thanks!


    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 outer join problem
    By softie in forum MySQL
    Replies: 5
    Last Post: August 31st, 09:27 PM
  3. Left Outer Join
    By Jared in forum MySQL
    Replies: 0
    Last Post: May 3rd, 03:31 AM
  4. Left join isn't joining
    By Bill in forum MySQL
    Replies: 7
    Last Post: December 23rd, 04:47 PM
  5. left join problem
    By AK in forum IBM DB2
    Replies: 8
    Last Post: August 8th, 12:16 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