help me with a query

Hello,

i have 2 tables, socios and socios_img, it's a db for members of car club.

The first table is where i have the id, name, make(of the car), year and a little text.
The second table i have id, id_socio(the same number of the 1st table so i can join them) and then i have foto_thumb and foto_big.

Now i wanted to do a query where the result would be: id, name, foto_thumb(only the first foto from that specific member).
Basicly i want to display everything from the first table but with an image from the car of the member, but the pictures are only on the second table.. and i want only the first picture from each member because the second table has more than 1 picture from each member
I hope i explain everything clear :)

CREATE TABLE `socios` (
`id` tinyint(4) NOT NULL auto_increment,
`nome` tinytext collate latin1_bin NOT NULL,
`carro` tinytext collate latin1_bin NOT NULL,
`ano` tinytext collate latin1_bin NOT NULL,
`texto` text collate latin1_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=3 ;

CREATE TABLE `socios_img` (
`id` tinyint(4) NOT NULL auto_increment,
`id_socio` tinyint(4) NOT NULL default '0',
`foto_thumb` tinytext collate latin1_bin NOT NULL,
`foto_big` tinytext collate latin1_bin NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=16 ;



Any help will be very apreciated.

Thanks

SELECT * FROM `socios` LEFT JOIN `socios_img` ON `socios`.`id` = `socios_img`.`id_socio` ORDER BY `socios_img`.`id` ASC LIMIT 1

that query only return 1 member.
i need it to return all members but only 1 photo from each one.

any idea?

What you want is called a co-related sub query.

If you were looking for the socios_img.id for each photo you would do it like this:


select
socios.id,
socios.name,
SI.foto_thumb
from
socios
LEFT JOIN
socios_img as SI
ON socios.id = SI.id_socio
where SI.id =
(select min(socios_img.id) from socios_img where socios_img.id = SI.id)

nop it's not this yet :/

maybe it can't be done with mysql querys and it's needed php coding, no?

let me give you an example

table socios:
id | nome | carro | ano | texto
1 | Rogério Machado | Fiat 600D | 1970 | É amarelo e é muito giro
2 | Margarida | fiat 600 | 1960 | vermelho descapotavel

table socios_img
id | id_socio | foto_thumb | foto_big
15 | 2 | img/socios/thumb_2_Tras.jpg | img/socios/big_2_Tras.jpg
14 | 2 | img/socios/thumb_2_Por do sol.jpg | img/socios/big_2_Por do sol.jpg
13 | 1 | img/socios/thumb_1_Nenufares.jpg | img/socios/big_1_Nenufares.jpg
11 | 1 | img/socios/thumb_1_Inverno.jpg | img/socios/big_1_Inverno.jpg

as you see the user rogerio has on db socios_img 2 images.. (id 15 and id 14)
user margarida has on db socios_img 2 images too..(id 13 and id 11)

I want to have as result:
1 | Rogério Machado | Fiat 600D | 1970 | É amarelo e é muito giro | 15 | 2 | img/socios/thumb_2_Tras.jpg | img/socios/big_2_Tras.jpg
2 | Margarida | fiat 600 | 1960 | vermelho descapotavel | 13 | 1 | img/socios/thumb_1_Nenufares.jpg | img/socios/big_1_Nenufares.jpg

an nothing else.
How can i do this? :cool:

Nope, no php is needed to solve this. I just wrote the query incorrectly.

Try this:


SELECT
socios.id,
socios.nome,
socios.carro,
socios.ano,
socios.texto,
socios_img.id,
socios_img.id_socio,
socios_img.foto_thumb,
socios_img.foto_big
FROM
socios
LEFT JOIN
socios_img
ON
socios.id = socios_img.id_socio
where socios_img.id =
(select max(DT.id) from socios_img as DT where DT.id_socio = socios_img.id_socio)


Note that the DT is just an alias for the socios_img table you can use whatever name you want there but change it in both places.

it worked :D
thanks a lot
i just didn't understand very well the query, can you explain it please ?
Thank you :)

sure. What happens with the subselect can be explained as follows (note that the optimiser doesn't necessarily peform the operation like this but this explanation should suffice).

for each value for:
socios_img.id_socio

all values of
socios_img.id
are examined and the largest of those values is returned.

the subquery compares the table to itself otherwise you couldn't check each value for socios_img.id

humm i see.. and where is the alias setted?
before "select max(DT.id) from socios_img as DT where DT.id_socio = socios_img.id_socio" theres no reference to DT..

correct, you set the alias within the subquery:
socios_img as DT

You can reverse it if you want:


SELECT
socios.id,
socios.nome,
socios.carro,
socios.ano,
socios.texto,
DT.id,
DT.id_socio,
DT.foto_thumb,
DT.foto_big
FROM
socios
LEFT JOIN
socios_img as DT
ON
socios.id = DT.id_socio
where DT.id =
(select max(id) from socios_img where socios_img.id_socio = DT.id_socio)


if that makes it more clear to you.

yep
thanks for the explanation.

by the way let me ask you another thing if you don't mind.

Lets say i have a table with
id | year | name
1 | 2007 | roger
2 | 2006 | bob
3 | 2006 | max
4 | 2005 | john
5 | 2005 | mike
6 | 2005 | peter

How can i query this db and have has result: 2007, 2006, 2005 only? (instead of 2007, 2006, 2006, 2005, 2005, 2005 :D

It's possible?

Thanks once again

the same as above, you only have to substitute in the correct column names.

SELECT id, ano
FROM actividades as AC
where AC.id =
(select max(id) from actividades)

only gives 2007 :/

SELECT id, ano
FROM actividades as AC
where AC.id =
(select max(id) from actividades where actividades.id = AC.id)

it's not this too :/

in this case i don't have 2 tables to compare.. how can it work? :(

SELECT id, ano
FROM actividades as DT
where DT.id =
(select max(id) from actividades where actividades.ano = DT.ano)

iuhuuuuuuu:D :D

it's working.. but it's well done?