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.

Anybody? Bueller? Bueller?


<a> wrote in message news:TfWdnWlQENNvhr_ZnZ2dnUVZ_uudnZ2d@comcast.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.
>

<a> wrote in message news:TOSdncbGgoXrWLnZnZ2dnUVZ_s-dnZ2d@comcast.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" <bill@karwin.com> wrote in message
news:e02ht50lrl@enews3.newsguy.com...
> <a> wrote in message news:TOSdncbGgoXrWLnZnZ2dnUVZ_s-dnZ2d@comcast.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?

<a> wrote in message news:cfmdnebNfKkX_LjZRVn-hg@comcast.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" <bill@karwin.com> wrote in message
news:e045b2017cl@enews2.newsguy.com...
> <a> wrote in message news:cfmdnebNfKkX_LjZRVn-hg@comcast.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.

<a> wrote in message news:jpadncqWGMmnR7jZRVn-sQ@comcast.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" <bill@karwin.com> wrote in message
news:e04ro409hg@enews3.newsguy.com...
> <a> wrote in message news:jpadncqWGMmnR7jZRVn-sQ@comcast.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!