:: Lyon :: Posted September 16, 2004 Share Posted September 16, 2004 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 More sharing options...
0 Mouton Posted September 16, 2004 Share Posted September 16, 2004 (edited) 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 September 16, 2004 by Mouton Link to comment Share on other sites More sharing options...
0 Mouton Posted September 16, 2004 Share Posted September 16, 2004 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 More sharing options...
0 :: Lyon :: Posted September 16, 2004 Author Share Posted September 16, 2004 (edited) 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 September 16, 2004 by maulia Link to comment Share on other sites More sharing options...
Question
:: Lyon ::
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