Help with custom query

[eluser]FrankieShakes[/eluser]
Hey all..

I'm having a damn difficult time trying to figure out how to write a custom query to pull information from 2 DB tables.

The breakdown is this:

I have 2 tables: contact and contact_tel_numbers.

A contact can have many telephone numbers, and a number has an optional "primary" flag. So a contact MAY/MAY NOT have a primary number specified.

What I would like to do is display a list of contacts, and for those that HAVE a primary, display that. For those who don't have a primary, I'd like to display ANY of the numbers associated with their ID.

Having done strictly client-side (JS, Ajax, etc.) development for the last 18 months, trying to wrap my brain around this query hasn't been easy.

I'd appreciate any help anyone can offer.


Thanks!
-f

[eluser]alexsancho[/eluser]
You can do it using join statements

Just an example:

Code:

SELECT * FROM contact, LEFT JOIN contact_tel_numbers ON contact.id=contact_tel_numbers.contact_id

[eluser]kgill[/eluser]
That join only covers to do half of what he wants, since he needs just the primary if they have it but otherwise everything else, I'd use a union and a subquery.

Code:

select x, y, z
from contact a, contact_tel_numbers b
where a.contact_id = b.contact_id
and b.primary_ind = 1
union
select x, y, z
from contact a, contact_tel_numbers b
where a.contact_id = b.contact_id
and a.contact_id not in (
select a.contact_id
from contact a, contact_tel_numbers b
where a.contact_id = b.contact_id
and b.primary_ind = 1
)

First select gets everyone who has a primary number set, next select grabs everybody else

- K

[eluser]champs[/eluser]
This is probably not relevant to your structure, but I can see someone searching the forum looking for this answer, so I'm putting it out there as knowledge to others who might be looking for that MySQL... maybe more DBs... have a COALESCE() function that, from left to right, finds a value to use.

Code:

SELECT c.* COALESCE(c.phone, t.phone1, t.phone2, t.phone3) as phone_num
FROM contacts AS c
JOIN contact_tel_numbers AS t ON c.contactid=t.contactid

So your result set would have a phone_num that looks first at c.phone, then t.phone1, etc. until it finds a value that isn't NULL and puts it in the result set as the phone_num field.

[eluser]FrankieShakes[/eluser]
[quote author="kgill" date="1192053283"]That join only covers to do half of what he wants, since he needs just the primary if they have it but otherwise everything else, I'd use a union and a subquery.

Code:

select x, y, z
from contact a, contact_tel_numbers b
where a.contact_id = b.contact_id
and b.primary_ind = 1
union
select x, y, z
from contact a, contact_tel_numbers b
where a.contact_id = b.contact_id
and a.contact_id not in (
select a.contact_id
from contact a, contact_tel_numbers b
where a.contact_id = b.contact_id
and b.primary_ind = 1
)

First select gets everyone who has a primary number set, next select grabs everybody else

- K[/quote]

Dude, you totally rock! That's what I was looking for! I had totally forgotten about "union". Truth be told, it's not something I used much in the past. I definitely need an SQL refresher.

Thanks so much, you saved me from pulling out my hair!

Quick question, if I wanted to add another "primary" field (from an email table), would I simply need to add another "union" query? Or is there more to it than just that?

[eluser]kgill[/eluser]
There's more to it than that, union basically merges the results of two queries into one so the number and type of the fields returned by each part have to be the same. That said it's entirely possible to do what you want via SQL but in this case it's probably easier to make two separate queries one for phone one for email and then just display them together via code.

- K