Solved

sql joins

Posted on 2011-02-28
4
267 Views
Last Modified: 2012-05-11
I need to join three tables. I want all 4 records from tableA and all matching records from tableB and tableC.  I end up with only 1 record.  In the end I only want 4 records and the data that matches.
SELECT   ui.ID, csct.description, csc.contact_type_acronym, csc.active, csc.date_added, ui.LastName + ', ' + ui.FirstName AS contactname, csct.acronym, csct.id as csctid
FROM         dbo.TableA AS csct LEFT OUTER JOIN   dbo.TableB AS csc ON csct.acronym = csc.contact_type_acronym 
LEFT OUTER JOIN dbo.TableC AS ui ON ui.ID = csc.contact_id
WHERE(csc.service_id = 196975) AND (csc.active = 1) AND (csct.ServiceLineID = '5') <-- this gives me the 4 records I need
ORDER BY csctid

Open in new window

0
Comment
Question by:lantervj
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 300 total points
ID: 34997573
Adding items to the where clause that are based on tables that are left joined basically results in an inner join on those columns.

If you move the 2 "csc.service_id = ..." and "csc.active = 1" clauses to the join portion, it should return the desired results.

SELECT   ui.ID, csct.description, csc.contact_type_acronym, csc.active, csc.date_added, ui.LastName + ', ' + ui.FirstName AS contactname, csct.acronym, csct.id as csctid
FROM         dbo.TableA AS csct LEFT OUTER JOIN   dbo.TableB AS csc ON csct.acronym = csc.contact_type_acronym and csc.service_id = 196975 and csc.active = 1
LEFT OUTER JOIN dbo.TableC AS ui ON ui.ID = csc.contact_id
WHERE  (csct.ServiceLineID = '5') <-- this gives me the 4 records I need
ORDER BY csctid

Open in new window

0
 
LVL 22

Assisted Solution

by:Thomasian
Thomasian earned 100 total points
ID: 34997577
SELECT ui.ID, csct.description, csc.contact_type_acronym, csc.active, csc.date_added, ui.LastName + ', ' + ui.FirstName AS contactname, csct.acronym, csct.id as csctid
FROM dbo.TableA AS csct LEFT OUTER JOIN
     dbo.TableB AS csc ON csct.acronym = csc.contact_type_acronym 
                          AND (csc.service_id = 196975) AND (csc.active = 1) LEFT OUTER JOIN
     dbo.TableC AS ui ON ui.ID = csc.contact_id
WHERE csct.ServiceLineID = '5'
ORDER BY csctid

Open in new window

0
 
LVL 8

Assisted Solution

by:raulggonzalez
raulggonzalez earned 100 total points
ID: 34997605
Hi,

Your problem cannot be the joins, the solution should be in the WHERE clause because you reference there

(csc.service_id = 196975) AND (csc.active = 1)

which don't belong to TableA csct ...

Do SELECT * and check manually the values to see it better.


Cheers
0
 

Author Closing Comment

by:lantervj
ID: 34997864
Fast, accurate results.  I like it.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question