Join problems: I'm missing something simple I think.

[Pandyon]

I don't write SQL that much, and usually my queries are pretty straight forward. I'm having a problem (I think it's join-related) where I want all the records from one table (A), but then if a related table (B) has a piece of data to add, I want it, but if not, I want it to return a blank or null value..but for some reason in my query, if the data doesn't exist in B, I get no A record.

here's the query. Basically i want Customer.Name no matter what (should give me about 40 records). And if cus_rel.related_name exists then I want it. otherwise, I'd like a null value in place. But in the query below there is one Customer that doesn't have a "big10" class code to match, so I only get 39 records in my result.


I've written queries like this, where I used sub-queries, and that works great, but in this case, I need TWO fields returned from CUS_REL, and sub-queries will only allow me to return a single value.

Any help appreciated. I think there's a simple answer to this, and I'm just not laying out my statement right.


SELECT CUSTOMER.NAME, CUS_REL.RELATED_NAME, CUS_REL.Rel_ID, CUS_DEM.DEM_CODE,
CUS_DEM.DEM_SUBCODE, CUSTOMER.MASTER_CUSTOMER_ID
FROM CUSTOMER INNER JOIN
CUS_REL ON CUSTOMER.MASTER_CUSTOMER_ID = CUS_REL.MASTER_CUSTOMER_ID INNER JOIN
CUS_DEM ON CUS_REL.RELATED_MASTER_CUSTOMER_ID = CUS_DEM.MASTER_CUSTOMER_ID AND
CUS_DEM.DEM_CODE = 'Division' AND CUS_DEM.DEM_SUBCODE = '01'
WHERE (CUSTOMER.CUSTOMER_STATUS_CODE = 'Active') AND (CUSTOMER.CUSTOMER_CLASS_CODE = 'Big10') AND
(CUSTOMER.NAME LIKE 'A%')
ORDER BY CUSTOMER.NAME

 

[RiverGuy]

You need to use an OUTER JOIN. For example:

SELECT a.*, b.SomeColumn
FROM MyTableIWantAllRecordsFrom a
LEFT OUTER JOIN
MyTableIWantValuesOrNullsFrom b
ON a.SomeCriteria = b.SomeCriteria

 

[Pandyon]

Nope. Tried that. Changed joins to Left Outer Joins, and tried all sorts of combinations, but still not getting the right number of records.

 

[hmckillop]

I think Riverguy was spot on with what he was saying - you probably need to define the relationship more between the tables.

The way I see it you have two options

SELECT  
	CUSTOMER.NAME, 
	CUS_REL.RELATED_NAME, 
	CUS_REL.Rel_ID, 
	CUS_DEM.DEM_CODE, 
	CUS_DEM.DEM_SUBCODE,
	CUSTOMER.MASTER_CUSTOMER_ID
FROM
	CUSTOMER 
LEFT JOIN CUS_REL 
	ON CUSTOMER.MASTER_CUSTOMER_ID = CUS_REL.MASTER_CUSTOMER_ID 
LEFT JOIN CUS_DEM 
	ON CUS_REL.RELATED_MASTER_CUSTOMER_ID = CUS_DEM.MASTER_CUSTOMER_ID 
		AND CUS_DEM.DEM_CODE = 'Division' 
		AND CUS_DEM.DEM_SUBCODE = '01'
WHERE     
	(CUSTOMER.CUSTOMER_STATUS_CODE = 'Active') 
	AND (CUSTOMER.CUSTOMER_CLASS_CODE = 'Big10') 
	AND (CUSTOMER.NAME LIKE 'A%')
ORDER BY CUSTOMER.NAME

Which in english says, give me all rows in Customer and if their is a match in CUS_REL give me them, and for CUS_REL give me if exists any matches in CUS_DEM

This returns

Allan	Brother	2	NULL	NULL	2
Andrew	Brother	1	Division	01	1
AskBrian	NULL	NULL	NULL	NULL	3
Asquith	uncle	3	NULL	NULL	4

Second option

SELECT  
	CUSTOMER.NAME, 
	CUS_REL.RELATED_NAME, 
	CUS_REL.Rel_ID, 
	CUS_DEM.DEM_CODE, 
	CUS_DEM.DEM_SUBCODE,
	CUSTOMER.MASTER_CUSTOMER_ID
FROM
	CUSTOMER 
LEFT JOIN CUS_REL 
	ON CUSTOMER.MASTER_CUSTOMER_ID = CUS_REL.MASTER_CUSTOMER_ID 
INNER JOIN CUS_DEM 
	ON CUS_REL.RELATED_MASTER_CUSTOMER_ID = CUS_DEM.MASTER_CUSTOMER_ID 
		AND CUS_DEM.DEM_CODE = 'Division' 
		AND CUS_DEM.DEM_SUBCODE = '01'
WHERE     
	(CUSTOMER.CUSTOMER_STATUS_CODE = 'Active') 
	AND (CUSTOMER.CUSTOMER_CLASS_CODE = 'Big10') 
	AND (CUSTOMER.NAME LIKE 'A%')
ORDER BY CUSTOMER.NAME

This returns
Andrew	Brother	1	Division	01	1

Which states give me any rows in customer, where a row does or doesnt exist in CUS_REL and where a row exists in CUS_DEM. (effectively an inner join across the tables)


For anyone wanting to try this - here is the setup stuff.

/*
create table customer 
(
	MASTER_CUSTOMER_ID int identity(1,1),
	[NAME] varchar(20),
	[CUSTOMER_STATUS_CODE] varchar(10),
	CUSTOMER_CLASS_CODE varchar(10)
)
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('Andrew', 'Active', 'Big10')--1
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('Allan', 'Active', 'Big10')--2
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('AskBrian', 'Active', 'Big10')--3
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('Asquith', 'Active', 'Big10')--4
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('Dave', 'Active', 'Big10')--5
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('John', 'Active', 'Big10')--6
INSERT into customer ([Name], [CUSTOMER_STATUS_CODE] , CUSTOMER_CLASS_CODE)
values ('Aaron', 'NotActive', 'Big1')--7

Create table CUS_REL 
	(	MASTER_CUSTOMER_ID int ,
		RELATED_MASTER_CUSTOMER_ID int,
		RELATED_NAME varchar(10), 
		Rel_ID int identity(1,1), )
INSERT INTO CUS_REL(MASTER_CUSTOMER_ID ,RELATED_MASTER_CUSTOMER_ID ,RELATED_NAME) values(1,3, 'Brother')--Andrew, AskBrian
INSERT INTO CUS_REL(MASTER_CUSTOMER_ID ,RELATED_MASTER_CUSTOMER_ID ,RELATED_NAME) values(2,5, 'Brother') -- Allan, Dave
INSERT INTO CUS_REL(MASTER_CUSTOMER_ID ,RELATED_MASTER_CUSTOMER_ID ,RELATED_NAME) values(4,7, 'uncle') --Asquith, Aaron

Create table CUS_DEM 
	(	MASTER_CUSTOMER_ID int,
		DEM_CODE varchar(10),
		DEM_SUBCODE varchar(10)
	
)

INSERT INTO CUS_DEM (MASTER_CUSTOMER_ID ,DEM_CODE ,DEM_SUBCODE ) values(3,'Division' , '01' )
INSERT INTO CUS_DEM (MASTER_CUSTOMER_ID ,DEM_CODE ,DEM_SUBCODE ) values(6,'Division' , '01' )

--drop table customer
--drop table CUS_DEM
--drop table CUS_REL

*/

"I'm living so far beyond my income that we may almost be said to be living apart