SELECT JOIN Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT JOIN Problem

Author

Topic

picans
Starting Member

4 Posts

Posted - 2008-08-27 : 09:42:41

Hi,
i had to tables. Entreprise and Contacts.

Enterprise:
ID
Name
Contact
....
Phone
Cell
FAX

Contact
ID
Entreprise_ID
Name
Phone
Cell
FAX

A Entreprise can have more than one Contact.

I want to make a SELECT that allos me to get the Enterprises wich have a Tfno Cell or FAX like a given number (as '555' for example) or in one of its contacts. (i.e Contacts.phone or contacts.cell ...)

I make this sentence but doesn't work
E=Enterprises
C=Contacts

SELECT E.id E.Name from Enterprises LEFT JOIN Contacts on C.Eid = Eid AND (C.phone like '%given_number%' OR C.fax like '%given_number%' OR C.cell like '%given_number%') WHERE E.phone like '%given_number%' OR C.fax like '%given_number%' OR C.cell like '%given_number%' ORDER by E.Name

Can someone help me to solve this issue

Many thnx in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 09:52:10

Show some data sample and explain the output you desire. what do you want in caes where Contact record doesnt exist for the Eid value?

picans
Starting Member

4 Posts

Posted - 2008-08-27 : 10:02:27

Enterprises
===========
Row 1: ID: 1, Name: Microsoft, Tfno: 555556443, FAX: -, Cell:933255552
Row 2: ID: 2, Name: Sun, Tfno: 552556443, FAX: -, Cell:931255352
Row 3: ID: 3, Name: Oracle, Tfno: 554556446, FAX: -, Cell:933255558

Contacts
========
Row 1: ID: 1,Eid: 1, Name: Bill Gates, Tfno: -, FAX: -, Cell:888888888
Row 2: ID: 2,Eid: 1, Name: John Terry, Tfno: -, FAX: -, Cell:7575757575
Row 3: ID: 3,Eid: 3, Name: John Adams, Tfno: 555212121, FAX: -, Cell:7575757575

If i search for '8888' i need to get
1, Microsoft

If i search '555' i need to get

1, Microsoft
3, Oracle

I expect that this was a good example :S

picans
Starting Member

4 Posts

Posted - 2008-08-27 : 10:05:24

If there is no entry on C table for Eid as in:

search: '931255352', i want to get

2, Sun

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:16:19

[code]SELECT e.ID,e.Name
FROM Enterprise e
LEFT JOIN Contact c
ON e.ID=c.Eid
AND (c.Tfno LIKE '%given_number%'
OR c.Fax LIKE '%given_number%')
WHERE e.Tfno LIKE '%given_number%'
OR c.Eid IS NOT NULL[/code]

picans
Starting Member

4 Posts

Posted - 2008-08-27 : 10:35:32

Many many thnx

"c.Eid IS NOT NULL" was the key :).

That is enough, but one last thing that would be the perfection,... ¿any hint to avoid duplicates in the response?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:43:08

quote:
Originally posted by picans

Many many thnx

"c.Eid IS NOT NULL" was the key :).

That is enough, but one last thing that would be the perfection,... ¿any hint to avoid duplicates in the response?

take DISTINCT

SELECT DISTINCT e.ID,e.Name
FROM Enterprise e
LEFT JOIN Contact c
ON e.ID=c.Eid
AND (c.Tfno LIKE '%given_number%'
OR c.Fax LIKE '%given_number%')
WHERE e.Tfno LIKE '%given_number%' 
OR c.Eid IS NOT NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources