CODEWITHSUNDEEP
sqlsql-serverdatabaserelational-database
Hunter
Hunter

Reputation: 572

Best way to structure SQL Query

I have several database tables:

I then have a junction table linking the two tables if required:

All of these tables are maintained with confirmed, accurate, clean data.

I have a fourth table that is used for informational purposes only and the data is neither clean, nor reliable as it is supplied by the end user and cannot be confirmed.

I want to write a query that returns Client/Employer data if a record exists in the Client_Employer table, but will also fallback to the ClientEmployer table for employer information if none exists otherwise.

The columns in Employer match exactly the same columns in ClientEmployer.

I have looked at several options using ISNULL(), CASE, IF/ELSE, etc. but just want to see what others think the best, cleanest way to do this will be?

Upvotes: 0

Views: 1918

Answers (3)

outis nihil
outis nihil

Reputation: 736

Well, making a few assumptions about the schema for ClientEmployer table, I'd combine a UNION and an EXISTS like this:

SELECT 
    cl.ClientID as ClientID, 
    em.EmployerID as employerID,
    cl.firstname, 
    cl.lastname, 
    em.employername, 
    em.fax, 
    em. address 
FROM 
    Client cl, 
    Employer em, 
    Client_Employer ce 
WHERE 
    cl.ClientID = ce.ClientID 
    and em.EmployerID = ce.EmployerID
UNION
SELECT 
    Clem.ClientID as clientID,
    -1 as EmployerID,
    clem.firstname, clem.lastname, 
    clem.employername, 
    clem.fax,
    clem.address 
FROM 
    ClientEmployer clem 

WHERE 
    NOT EXISTS (
                  SELECT * FROM Client cl, Employer em, Client_Employer ce 
                  WHERE cl.ClientID = ce.ClientID 
                        and em.EmployerID = ce.EmployerID 
                        and clem.ClientID = cl.ClientID
                        and clem.EmployerName = ce.EmployerName)

Upvotes: 1

Kevin Matlock
Kevin Matlock

Reputation: 99

What about the usage of IF EXISTS? Seems like you could pretty easily retrieve data where it exists, else select from ClientEmployer in your example where it does not exist.

Upvotes: 0

Felypp Oliveira
Felypp Oliveira

Reputation: 2187

I think you should use ISNULL() to LEFT JOIN Employer, like this:

SELECT 
  Client.*, 
  Employer.* 
FROM Client 
LEFT JOIN Client_Employer 
 ON Client_Employer.client_id = Client.id 
LEFT JOIN ClientEmployer 
 ON ClientEmployer.client_id = Client.id 
LEFT JOIN Employer 
 ON Employer.id = ISNULL(Client_Employer.employer_id, ClientEmployer.employer_id) 
WHERE Employer.id IS NOT NULL;

If some Employer.id is NULL (WHERE clause) so there aren't a relationship from a given Client.id in both Client/Employer tables, which means you'll have just the data which is in some of the Client/Employer tables.

Hope it works as you expect.

Upvotes: 0

Related Questions