Help with querying mutiple fields, filtering and outputing to single

[Coza73]

I am trying to construct a query where the source is 3 fields one with a name and 2 with phone numbers, I want to filter out a specific type of number (mobile) from both fields and output it as a list of names and unique numbers starting with 04. See structure below:-

Name | phone | mobile
---------------------
Cory |04122 |
Glenn |04123 |04123
Barry |91234 |04124
Bryan |91235 |
Darren |91236 |04125
Frank |04126 |04126
Lauren |91237 |04127

What I would like to do is output only the names with the number starting with 04, if the 04 number is in both fields only output one.

Any help will be appreciated.

Thanks

Cory

 

[r937]

the sql for what you are asking is more complex than it needs to be

you should really redesign your table --

Name   | ptype  | pnumber
-------------------------
Cory   | phone  | 04122
Glenn  | phone  | 04123
Barry  | phone  | 91234
Barry  | mobile | 04124
Bryan  | phone  | 91235
Darren | phone  | 91236
Darren | mobile | 04125
Frank  | phone  | 04126
Lauren | phone  | 91237
Lauren | mobile | 04127

then your query will be a lot easier

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon