0

I have following query that works correctly:

select * from myTable a where a.company is null and exists (select b.company from myTable b where b.id = a.id and b.office_id = a.office_id and b.company is not null);

Now, I also want to display the field value b.company from the subquery next to the fields from myTable a.

How do I get this done?

Thank you and best regards

AdamMUC
  • 13
  • 3

1 Answers1

1

If you want results from multiple tables you should join the tables together. Since you want only records from A that exist in B, you need to use an outer JOIN returning all records from A and only those matching in B. But then you want to exclude all those records from A that were not found in B.

SELECT *, b.company
FROM  myTable a 
LEFT JOIN myTable B 
  ON b.id = a.id 
 and b.office_id = a.office_id
 and b.company_ID is not null
WHERE a.company is null 
  and B.ID is not null and B.office_ID is not null --this handles the exists part.
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • Wow I was off on joins a few times... should be correct now. – xQbert Aug 19 '15 at 15:09
  • okay, this works very good. but how do I update a.company to value of b.company with this result by using sql without looping through resultset? Thank you. – AdamMUC Aug 26 '15 at 10:17
  • See examples of that thoughout the site: here's one http://stackoverflow.com/questions/15209414/mysql-update-join or http://stackoverflow.com/questions/806882/update-multiple-tables-in-mysql-using-left-join If you're struggling implementing those with the above ask a different question, I'm positive you'll get the help. We try and keep each question independent of others so each question/answer pair addresses specific needs. By addressing a follow up here, it complicates the question and makes it less valuable to others. – xQbert Aug 26 '15 at 12:24