[ENG] linked table multiple occurences problem

I've 2 tables linked by a simple ID.

Problem is that for the same ID of table 1 I can have many occurences in
table 2.
How to get a "select" wich only return the first occurence in table 2 ?

let's make an example.

table1
A 1
B 2
C 3

table2
1 XXX
1 YYY
2 AAA
3 EEE
1 GGG

select * from table1 left join table2 on table1.field2 = table2.field1

in my case I've
A 1 XXX
A 1 YYY
A 1 GGG
B 2 AAA
C 3 EEE

I want to avoid the YYY and the GGG (I only want one occurence of the
table1.field1 per query result.

I've tried with distinct, but it does return all different results.
I can't change table structure !

Thanks for help.

Bob

Re: [ENG] linked table multiple occurences problem

Bob Bedford wrote:
> I've 2 tables linked by a simple ID.
>
> Problem is that for the same ID of table 1 I can have many occurences in
> table 2.
> How to get a "select" wich only return the first occurence in table 2 ?

Which one is "the first"? Meaning: How do you define "the first record"?
The smallest? The shortest?

> let's make an example.
>
> table1
> A 1
> B 2
> C 3
>
> table2
> 1 XXX
> 1 YYY
> 2 AAA
> 3 EEE
> 1 GGG
>
> select * from table1 left join table2 on table1.field2 = table2.field1
>
> in my case I've
> A 1 XXX
> A 1 YYY
> A 1 GGG
> B 2 AAA
> C 3 EEE
>
> I want to avoid the YYY and the GGG (I only want one occurence of the
> table1.field1 per query result.

If you don't care, you can take the minimum or the maximum, e.g.:
SELECT t1.field1, MIN(t2.field1)
FROM table1 t1
LEFT JOIN table2 t2 ON t2.field1 = t1.field2
GROUP BY t1.field1

But I would use INNER JOIN instead of LEFT JOIN, if possible.


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786

Re: [ENG] linked table multiple occurences problem

"Dominik Echterbruch" a écrit dans le message de
news: 1134572387.99662.0@dyke.uk.clara.net...
> Bob Bedford wrote:
>> I've 2 tables linked by a simple ID.
>>
>> Problem is that for the same ID of table 1 I can have many occurences in
>> table 2.
>> How to get a "select" wich only return the first occurence in table 2 ?
>
> Which one is "the first"? Meaning: How do you define "the first record"?
> The smallest? The shortest?
>
>> let's make an example.
>>
>> table1
>> A 1
>> B 2
>> C 3
>>
>> table2
>> 1 XXX
>> 1 YYY
>> 2 AAA
>> 3 EEE
>> 1 GGG
>>
>> select * from table1 left join table2 on table1.field2 = table2.field1
>>
>> in my case I've
>> A 1 XXX
>> A 1 YYY
>> A 1 GGG
>> B 2 AAA
>> C 3 EEE
>>
>> I want to avoid the YYY and the GGG (I only want one occurence of the
>> table1.field1 per query result.
>
> If you don't care, you can take the minimum or the maximum, e.g.:
> SELECT t1.field1, MIN(t2.field1)
> FROM table1 t1
> LEFT JOIN table2 t2 ON t2.field1 = t1.field2
> GROUP BY t1.field1

Great, this works like a charm. Thanks for the help.

> But I would use INNER JOIN instead of LEFT JOIN, if possible.
Can't be done, in some cases there is no "table2" record associated. That's
why the left join is for...

Thanks for help.

Bob

Re: [ENG] linked table multiple occurences problem

Bob Bedford wrote:
>
>> SELECT t1.field1, MIN(t2.field1)
>> FROM table1 t1
>> LEFT JOIN table2 t2 ON t2.field1 = t1.field2
>> GROUP BY t1.field1
>
> Great, this works like a charm. Thanks for the help.

In MySQL you can even omit the MIN() function. But please read [1]
carefully before doing this. You'll receive unpredictable results - what
might be what you want...

[1] http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-field s.html


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786