Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts

    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

    Code:
    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

  • #2
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,647
    Thanks
    2
    Thanked 406 Times in 398 Posts
    Code:
    SELECT * FROM `socios` LEFT JOIN `socios_img` ON `socios`.`id` = `socios_img`.`id_socio` ORDER BY `socios_img`.`id` ASC LIMIT 1

  • #3
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    that query only return 1 member.
    i need it to return all members but only 1 photo from each one.

    any idea?

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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:

    Code:
    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)

  • #5
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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?

  • #6
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Nope, no php is needed to solve this. I just wrote the query incorrectly.

    Try this:

    Code:
    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.

  • #7
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    it worked
    thanks a lot
    i just didn't understand very well the query, can you explain it please ?
    Thank you

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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

  • #9
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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..

  • #10
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    correct, you set the alias within the subquery:
    socios_img as DT

    You can reverse it if you want:

    Code:
    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.

  • #11
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

    It's possible?

    Thanks once again

  • #12
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    the same as above, you only have to substitute in the correct column names.

  • #13
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    SELECT id, ano
    FROM actividades as AC
    where AC.id =
    (select max(id) from actividades)

    only gives 2007 :/

  • #14
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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 :/

  • #15
    Regular Coder
    Join Date
    Jul 2006
    Posts
    119
    Thanks
    1
    Thanked 0 Times in 0 Posts
    in this case i don't have 2 tables to compare.. how can it work?


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •