0

I have the following two tables:

       table1
        id
        name
        ownedby


       table2
        id
        ownername

Here, in table1, ownedby can be 0 (to mean it is not owned by anyone) or refers to id column of table2. Now what I want is to get data from table1 but get ownername from table2 if owenedby>0

The output basically is to be:

     1  0 ==>owned by no one
     2  Barka =======> owned by barka
Nie Selam
  • 1,343
  • 2
  • 24
  • 56
  • What does id represents in table1? – Harshil Doshi Oct 24 '17 at 13:40
  • you can use joins. https://stackoverflow.com/questions/6294778/mysql-quick-breakdown-of-the-types-of-joins – Taha Paksu Oct 24 '17 at 13:41
  • Hi @Harshil, it is an auto-number column. But the tables can't be related at all since many have no corresponding record in table 2. – Nie Selam Oct 24 '17 at 13:41
  • thanks @TahaPaksu but the tables are not related so join operation won't work, would it?. And cross join seems an overkill – Nie Selam Oct 24 '17 at 13:43
  • 1
    they seem related to me. table1.ownedby -> table2.id – Taha Paksu Oct 24 '17 at 13:44
  • tables are not related ? What do you mean? If in some cases the ID's are in both tables then you can left join. – isaace Oct 24 '17 at 13:44
  • Hey Taha, true but like I said, sometimes ownedby will be 0 when the record has no "owner" (i.e. public ppty concept). On other times, yes it will be ownedby=table2.id. If they were always related, i won't be struggling. – Nie Selam Oct 24 '17 at 13:46
  • 1
    isn't this a trivial job for `left join` and `ifnull(t2.ownername, 'no one')`? – underscore_d Oct 24 '17 at 13:47

1 Answers1

1

Following query should work (though I didn't tested it):

select t1.id, t1.name,
(case when t1.ownedby = 0 then 'No one'
     else t2.ownername
     end) as Ownername
from table t1
Left Join table t2
on t1.ownedby = t2.id
group by t1.id;

Hope it helps!

Harshil Doshi
  • 3,497
  • 3
  • 14
  • 37