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
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