CODEWITHSUNDEEP
mysql
oderfla
oderfla

Reputation: 1797

Getting one row from left join

Probably bad title, but I could not find better short description.

These are the tables Im working with:

Table Objects:

ID
NAME

Table Geo:

ID
NAME
PARENT_ID (int)
IDENTIFIER (varchar)

Table ObjectsGeo:

ID
OBJECT_ID
GEO_ID

So I insert some data in "objects":

insert into objects values(1,'Jack');   
insert into objects values(2,'Penny');

And then in "geo":

insert into geo values(1,'USA', null, '001');   
insert into geo values(2,'New York', 1, '1001');    
insert into geo values(3,'England', null, '002');   
insert into geo values(4,'London', 3, '1002');

If Jack lives in New York, then I will insert it this way:

insert into ObjectsGeo values(1,1,1);   
insert into ObjectsGeo values(2,1,2);

If you tell me this is a bad database design, I will agree (somebody else coded this). However, this is what I have now and making changes in the design could be complicated/cause bugs somewhere else.

Now, when I need to fetch Jack from database, as I also want to know where he lives, I do something quite ugly, which is having a left join to also include "ObjectsGeo". I then only take the first row (sigh) and "manually" put city and country in the object with programming code. Is there a way, with only one query, to get the row for Jack including 2 fields, one for country and one for city?

----EDIT---- The tables were more or less pseudocode. The query itself (also as pseudocode) should be:

select o.*, g.identifier from objects o left join ObjectsGeo og on og.object_id = o.id left join Geo g on g.id = og.geo_id where o.id = 1;

Upvotes: 0

Views: 37

Answers (1)

Harshil Doshi
Harshil Doshi

Reputation: 3592

Following query will get the job done:

select o.name, 
       (select name from geo where id = min(og.geo_id)) as geo_country, 
       (select name from geo where id = max(og.geo_id)) as geo_city
from objects o
inner join objectsgeo og
on og.object_id = o.id
inner join geo g
on g.id = og.geo_id
group by og.object_id;

Click here for Demo

If you've any doubt(s), feel free to ask by commenting below.

Hope it helps!

Upvotes: 1

Related Questions