Gossamer Forum
(Advanced Search)
for
Home : General : Databases and SQL :

Limit Inner Join

Nov 2, 2003, 7:52 AM
cK
Enthusiast (567 posts)
Nov 2, 2003, 7:52 AM
Post #1 of 5
Views: 12693
Shortcut
Quote Reply
Limit Inner Join
 
Hi,

The sql statement stated at the bottom of this post currently returns:

Code:
1 : 10 : home
2 : 22 : office
3: 33 : home
3: 33: office
4: 44: home
5: 55: office

That's an contact id number, an contactperson id number and the type of adress that is in the database. The problem is only that if two types of adresses are listed in contactpersonsaddress I get two lines in the above result. And I would like to have "IF OFFICE ADDRESS THEN OFFICE ADRESS ELSEIF HOME ADRESS HOME ADRESS ELSE NOTHING"

Who can help me out? Suggest some good reference websites to learn more about these types of inner joins? Thanks in advance,

cK

Code:
SELECT contacts.id, contactpersons.id, contactpersonsadress.type
FROM contactpersons
INNER JOIN contacts ON (contactpersons.id = contacts.id) AND ('client' = contacts.type)
INNER JOIN contactpersonsaddress ON (contactpersons.id = contactpersonsaddress.id) AND (('office' = contactpersonsaddress.type) OR ('home' = contactpersonsaddress.type))
ORDER BY ...
Nov 4, 2003, 2:46 PM
cK
Enthusiast (567 posts)
Nov 4, 2003, 2:46 PM
Post #2 of 5
Views: 12242
Shortcut
Quote Reply
Re: [cK] Limit Inner Join In reply to
Is this the wrong forum? Or did I not ask the right question? Please advise.
Nov 8, 2003, 8:04 AM
cK
Enthusiast (567 posts)
Nov 8, 2003, 8:04 AM
Post #3 of 5
Views: 12224
Shortcut
Quote Reply
Re: [cK] Limit Inner Join In reply to
Code:
SELECT contacts.id, contactpersons.id, contactpersonsadress.type FROM contactpersons
INNER JOIN contacts ON (contactpersons.id = contacts.id) AND ('client' = contacts.type)
INNER JOIN contactpersonsaddress ON (contactpersons.id = contactpersonsaddress.id) AND ( IF ('office' = contactpersonsaddress.type) ELSE ('home' = contactpersonsaddress.type))
ORDER BY ...

Why isn't this possible? Pirate

Last edited by:

cK: Nov 8, 2003, 8:06 AM
Nov 8, 2003, 10:58 AM
Stealth
Veteran (17240 posts)
Stealth's avatar
Nov 8, 2003, 10:58 AM
Post #4 of 5
Views: 12165
Shortcut
Quote Reply
Re: [cK] Limit Inner Join In reply to
You need to put those conditions in a WHERE clause:

Code:
WHERE (something = something)

Alternatively, you could use the conditions within your output codes and I can't really provide an example since I don't know what programming language you are using in your script..
========================================
Buh Bye!

Cheers,
Me
Nov 8, 2003, 1:32 PM
cK
Enthusiast (567 posts)
Nov 8, 2003, 1:32 PM
Post #5 of 5
Views: 12121
Shortcut
Quote Reply
Re: [Stealth] Limit Inner Join In reply to
 
It's working now in SQL! (...as I needed a solution 100% in SQL).

Code:

SELECT contacts.id, contactpersons.id, contactpersonsadress.type,
Address =
CASE
WHEN LEN(homeaddress.address) > 1 THEN homeaddress.address
WHEN LEN(officeaddress.address) > 1 THEN officeaddress.address
ELSE ''
END

FROM contactpersons
INNER JOIN contacts ON (contactpersons.id = contacts.id) AND ('client' = contacts.type)
LEFT OUTER JOIN contactpersonsaddress as officeaddress ON (contactpersons.id = contactpersonsaddress.id) AND ('office' = contactpersonsaddress.type)
LEFT OUTER JOIN contactpersonsaddress as homeaddress ON (contactpersons.id = contactpersonsaddress.id) AND ('home' = contactpersonsaddress.type)
ORDER BY ...