• 0

Help with an SQL query


Question

If I have table say table A

==========

table A

==========

id

contactid

companyid

==========

I also have table contact and company

======

contact

======

id

name

address

======

======

company

======

id

name

address

=======

Table A might contains:

==================

id | contactid | companyid

1 | 0 | 21

2 | 51 | 0

3 | 3 | 0

How do I write an SQL query (mySQL), something like:

For every record,

if contactid != 0, grab contact name and address

if companyid != 0, grab company name and address instead

After that, I want to combine both results into one so I can process each record accordingly. Any ideas? :(

Link to comment
Share on other sites

3 answers to this question

Recommended Posts

  • 0

Well, you could use a 'trick':

SELECT CONCAT(contact.name,company.name) AS name, CONCAT(contact.address,company.address) AS address FROM A LEFT JOIN contact ON (A.contactid = contact.id) LEFT JOIN company ON (A.companyid=company.id);

Will work as long as u don't have rows with non-empty data with id=0 in contact and company tables.

Edited by Mouton
Link to comment
Share on other sites

  • 0

and hum... to simplify matters, why don't u use only one table for both companies and contacts ?

would make sense since u store the same information...

A

---

id

contactid

contact

---------

contactid

is_company

name

address

is_company would be an enum (Y or N) to say if it's a company or a contact ...

To simpify matters even more, u could put everything in the same table, since it's a 1 for 1 relation... :)

Link to comment
Share on other sites

  • 0

Well, it actually has much more than that. Just for simplicity, I only put a few fields.

In fact, the contact will have firstname, surname, address, suburb, phoneno, etc.....

Company will have more.. name, address, suburb, url, ABN number, faxno, etc..

I'm currently doing a telemarketing module for a software company and they might call a company or a person. So I have to get the right data accordingly :)

Thx for the suggestions you've given before. It actually helps me to think more about this

As per your 2nd suggestions, I can do that too, but that means there will be empty fields and also what happens if a contact works in multiple companies.. that might become a headache too :)

Edited by maulia
Link to comment
Share on other sites

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.