SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation mysql query help needed

    i have 3 tables
    table A with A.id ...
    table B with B.id, B.A_id
    table C with C.id, C.A_id, C.type

    table B and C can contain more records with A_id.

    i need list of all record from A (regardless if there are records for A_id in table B or C),
    record containing A_id in table B with max from B.id (B.id from the records containing A_id for every A.id),
    record containing A_id in table C with type=o and max from C.id (C.id from the records containing A_id for every A.id)

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    select a.id,
           max(b.id) as maxbid, 
           max(c.id) as maxcid
      from a
      left join b
        on a.id = b.a_id
      left join c
        on a.id = c.a_id
       and c.type = 0
     group by a.id

  3. #3
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for the help, but i already tried that query and it does not return the correct results

    the condition c.type = 0 is disregarded while max(c.id)
    and if c.something is stated in the select c.something is not from the max(c.id) but from the first found record which is min(c.id)

  4. #4
    SitePoint Member
    Join Date
    Dec 2010
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry my bad about the type=0 disregarding (this happens if i join the table C table with table D and the d.type=0 - but this is not the problem)

    the query returns the c.id but c.something is incorrect

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by kiprijan View Post
    and if c.something is stated in the select c.something is not from the max(c.id) but from the first found record which is min(c.id)
    yeah, but there was no c.something in your original post, you just added it

    and of course the query no longer works the way you want if you add it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Member
    Join Date
    Jan 2011
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select a.name,a.aid from
    author a,catalog c where a.aid=a.aid
    and c.bid in (select bid from orderdetails group by bid having(qty) in (select max(sum(qty)) from orderdetails having sum(qty) group by bid));

    the error is ERROR 1111 (HY000): Invalid use of group function

    please help

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by srinathn1 View Post
    please help
    sure

    do you know what the GROUP BY clause does?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •