1

Using an SQL JOIN example from StackOverflow, What is the difference between "INNER JOIN" and "OUTER JOIN"?

Would like to know if this is returned from a Store-procedure, how would return distinct value name and phone number from the example below. Even if I create an XML string, even then I cant select DISTINCT values. Any help?

select name, phone, selling 
from people join property 
on people.pid = property.pid;

+-----------+--------------+----------------------+
| name      | phone        | selling              |
+-----------+--------------+----------------------+
| Mr Brown  | 01225 708225 | Old House Farm       |
| Mr Pullen | 01380 724040 | The Willows          |
| Mr Pullen | 01380 724040 | Tall Trees           |
| Mr Pullen | 01380 724040 | The Melksham Florist |
+-----------+--------------+----------------------+
Community
  • 1
  • 1
Tech Bangalore
  • 43
  • 1
  • 6
  • 1
    I'm sorry I can't understand your question well. Are you trying to return distinct values from that Query or what? – Baso Feb 03 '16 at 12:45
  • This is distinct values, depends on what you want – sagi Feb 03 '16 at 12:46
  • Something like `select name, phone, MAX(selling) selling from people join property on people.pid = property.pid GROUP BY name, phone`? Just a "random" value from the selling list if there are many for a name/phone#? – Joachim Isaksson Feb 03 '16 at 12:47
  • @Tech Bangalore when you post a "how to do in SQL" question its always best to specify your input and expected output. That way even if your wording isn't comprehensible users get a good idea of what you want to achieve. – uncaught_exception Feb 03 '16 at 12:49
  • Well, combination of name, phone and selling is distinct, so 4 rows will be returned, now in case you need only name and phone to be distinct, then solutions are given below. :) – Incredible Feb 03 '16 at 12:55

1 Answers1

-1

It's actually a bit unclear

But if you do someting like Select distinct name,phone from people join property on people.pid = property.pid;

you gonna to have Mr Brown | 01225 708225 | | Mr Pullen | 01380 724040

airliquide
  • 520
  • 7
  • 16
  • You just copied my answer – sagi Feb 03 '16 at 12:56
  • We just post on the same time ;) – airliquide Feb 03 '16 at 12:57
  • Thanks @Gordon Linoff for formatting the table. Reiterating my question, I wanted all the rows matched by the LEFT JOIN, but each column to have distinct values, as, from the table above, Mr Pullen and his phone appears only once since they are the same for the 3 records. The SP I have has some complex JOINS, I need it to return output with distinct values for each column. I'm OK if the output is returned as an XML string. Is this achievable? – Tech Bangalore Feb 05 '16 at 08:28