Selecting data from different rows in the same table with a single statement

Question

I'm selecting data from two different rows in the same table using a single sql.

"id"    "borrowMax" "holder"    "category"  "country"
"1"     "2"         "0"         "3"         "US"
"2"     "0"         "1"         "10"        "US"

What I'm trying to do works out to this.

select id, holder from mytable where id = 2
select borrowMax from mytable where id = (
        holder from the above select, in this case it's 1
) and category = 3

The way I do it after looking at examples online is

SELECT col1.id, col1.holder, col2.borrowMax
FROM collection_db col1
JOIN collection_db col2
ON col2.holder = col1.id
WHERE col1.id = 2 //Here, 2 is the value supplied by me
AND col2.category = 3

Sure, this works. But since it's something I pieced together myself, I have my doubts. How would you do something like this? Am I on the right track? (I'm sure I'm not).

looks ok to me - and if it gives you the results you want then it should be good. — Ian Kenney
– Ian Kenney, Commented Jun 21, 2013 at 16:55
@IanKenney I had doubts regarding the JOIN. Should it be INNER JOIN? — jmenezes
– jmenezes, Commented Jun 21, 2013 at 17:02
JOIN is OK: when you specify the ON ... condition, JOIN is equivalent to INNER JOIN. It's more or less explained here. — Ed Gibbs
– Ed Gibbs, Commented Jun 21, 2013 at 17:13

Manish Jain · Accepted Answer · 2013-06-21 16:59:16Z

3

You can also use table alias for this.

select t1.id, t1.holder, t2.borrowMax from mytable t1, mytable t2 where t1.id = t2.holder

answered Jun 21, 2013 at 16:59

Manish Jain

8791 gold badge9 silver badges20 bronze badges

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

Comments

face · Accepted Answer · 2013-06-21 21:03:02Z

1

I would make use of nested select statements for a use case like yours. There is no JOIN operation being used, just a select query over a already filtered set of results and a logically more coherent code.

SELECT borrowmax, holder, id FROM mytable WHERE 
  id = (SELECT holder FROM mytable WHERE id = 2 )
    AND category = 3

edited Jun 21, 2013 at 21:03

answered Jun 21, 2013 at 19:14

face

1,49513 silver badges28 bronze badges

Comments

Kiril Dobrev · Accepted Answer · 2020-09-25 18:55:49Z

0

I had to find What is the difference in population between 2000 and 2010 in Indonesia? Table columns:

country STRING
population  NUMBER
year    NUMBER

This is the query that gave me the result I was looking for:

SELECT b.country,
(b.population - a.population) 
AS diff_population
  FROM population_years a
  JOIN population_years b ON b.country = a.country
WHERE b.country = 'Indonesia'
  AND a.year = 2000
  AND b.year = 2010;

edited Sep 25, 2020 at 18:55

answered Sep 25, 2020 at 18:48

Kiril Dobrev

8691 gold badge9 silver badges13 bronze badges

Collectives™ on Stack Overflow

Selecting data from different rows in the same table with a single statement

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related