Use result from SQL query in different table

Question

If I have three different tables like this

table_1

Field 1: victories
Field 2: name

table_2

Field 1: name
Field 2: birthday

Now I would want to get the birthday of the person with the most victories.

So I would do something like this (pseudo code):

select victories from table_1 and sum_it_all
get name and pass name to table_2
select birthday from table_2 where name

Ok, this is pretty ugly pseudo code, but I hope you get the point.

Using Andomar's solution works fine. Now I tried to nest another table in it, like this though:

select address
from table_3
where birthday = 
    (
    select  birthday
    from    table_2
    where   name = 
            (
            select  name
            from    table_1
            group by
                name
            order by
                sum(victories) desc
            limit   1
            )
    )

I do get a correct answer, but for some reason also get a null back. And how would I output the sum of victories?

What is your question on this? this is looking like you have answer of your question .. — pratik garg
– pratik garg, Commented Jun 25, 2012 at 18:50

Andomar · Accepted Answer · 2012-06-25 18:54:48Z

1

select  birthday
from    table_2 t2
where   name = 
        (
        select  name
        from    table_1 t1
        order by
                victories desc
        limit   1
        )

If one user can have multiple rows in table_1, you'd have to sum the victories:

select  birthday
from    table_2 t2
where   name = 
        (
        select  name
        from    table_1 t1
        group by
                name
        order by
                sum(victories) desc
        limit   1
        )

edited Jun 25, 2012 at 18:54

answered Jun 25, 2012 at 18:48

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Brian Driscoll Over a year ago

where are the victories summed?

Andomar Over a year ago

This answer assumes a 1:1 relation between table_1 and table_2. I'll add a version for a 1:many relation

Joshua Cohan Over a year ago

Thanks. It works for two tables. Can you take a look at my initial post again?

Andomar Over a year ago

Brian Driscoll's answer will print the sum of victories. As for the null, check the content of table_3 ? Does it contain null address for the top name?

Brian Driscoll · Accepted Answer · 2012-06-25 18:55:58Z

1

I think what you're looking for is something like this:

SELECT t2.name, SUM(t1.victories) as SumOfVictories, t2.birthday 
FROM table_1 as t1
JOIN table_2 as t2
ON table_1.name = table_2.name
GROUP BY t2.name, t2.birthday
ORDER BY SUM(t1.victories) DESC
LIMIT 1

edited Jun 25, 2012 at 18:55

answered Jun 25, 2012 at 18:50

Brian Driscoll

19.7k3 gold badges51 silver badges66 bronze badges

1 Comment

swasheck Over a year ago

Good answer. +1 from me. I'm deleting mine out of a clear misunderstanding of the OP request.

Vikram · Accepted Answer · 2012-06-25 19:09:56Z

1

You can use following nested SQL:

 select name, birthday from table_2 where name in (
   select name from table_1 order by victories desc limit 1
   )

answered Jun 25, 2012 at 19:09

Vikram

4,1828 gold badges47 silver badges71 bronze badges

Comments

johnmadrak · Accepted Answer · 2012-06-25 18:51:04Z

0

Would SELECT * FROM table_1 INNER JOIN table_2 ON table_1.name=table_2.name ORDER BY victories DESC give you the desired results?

answered Jun 25, 2012 at 18:51

johnmadrak

8255 silver badges7 bronze badges

Comments

nkr · Accepted Answer · 2012-06-25 19:13:19Z

0

This might be a solution for your problem:

SELECT table_1.name, table_2.birthday
FROM table_2
JOIN table_1 ON table_1.name=table_2.name
WHERE table_1.victories>=ALL(SELECT table_1.victories
                             FROM table_1)

answered Jun 25, 2012 at 19:13

nkr

3,0587 gold badges33 silver badges39 bronze badges

Comments

Charles Bretana · Accepted Answer · 2012-06-25 23:51:56Z

-1

try this:

   Select name, birthday from table_2 t2
       Join table_1 t1 On t1.Name = t2.name
   Having Count(*) = 
         (Select Max(namCount)
          From (Select Count(*) namCount
                From table_1
                Group By name))
   Group By t1.name

edited Jun 25, 2012 at 23:51

answered Jun 25, 2012 at 18:56

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

2 Comments

Andomar Over a year ago

I don't think you can nest aggregates like max(count(*)) ?

Charles Bretana Over a year ago

correct, I omitted second subquery when I transcribed concept from brain to website!

Collectives™ on Stack Overflow

Use result from SQL query in different table

6 Answers 6

4 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related