We help IT Professionals succeed at work.

join table

Hello,

I have to join my current query that joins two tables to a third table.

This third table contains two columns I am interested in retrieving: comments and a last contact date

Some records in the third table may not have any comments or last contact date values for some customers

The joining fields of table 3 are: customer_id, entity_id, invoice_id

I am attaching my current query (the one with two tables)

SELECT
b.name AS 'CUSTOMER NAME',
a.journal_customer_id AS 'CUSTOMER ID',
a.entity_id AS 'ENTITY ID',
a.recv_invoice_id AS 'INVOICE ID',
a.invoice_date AS 'INVOICE DATE'
a.amount_15 AS 'AMOUNT',
FROM RG_AR_COLLECTION a
JOIN RECEIVABLES_CUSTOMER b
ON b.id = a.journal_customer_id
WHERE a.entity_id IN ('10')  < this will be a dynamic value - eventually.
AND a.related_currency_id = 'USD'

and the new one (the one that gets just the two new columns from the third table)

SELECT
COMMENTS AS 'REMARKS',      
LAST_CONTACT_DATE AS 'LAST CONTACT DATE'
FROM RG_AR_COLLECTIONS_LOG


If I could get help joining to this table without getting duplicates.

my record should be something like:


customer name, customer id, entity id, invoice id, invoice date, amount, remarks (it is ok if no value), last contact date (it is ok if no value)

I added alias to the columns on the first query so it is easy to identify the columns to join to.

Thank you very much for your help.
Comment
Watch Question

If the 3rd table may have multiple records with the same customer_id, entity_id, invoice_id (set an "ORDER BY" clause in the sub-query to determine which record is selected in this case, i.e., descending on a datetime or something):

SELECT 
b.name AS 'CUSTOMER NAME',
a.journal_customer_id AS 'CUSTOMER ID',
a.entity_id AS 'ENTITY ID',
a.recv_invoice_id AS 'INVOICE ID',
a.invoice_date AS 'INVOICE DATE',
a.amount_15 AS 'AMOUNT',
c.COMMENTS AS 'REMARKS',       
c.LAST_CONTACT_DATE AS 'LAST CONTACT DATE' 

FROM RG_AR_COLLECTION a

INNER JOIN RECEIVABLES_CUSTOMER b
ON b.id = a.journal_customer_id

LEFT OUTER JOIN (
    select top 1
    COMMENTS,       
    LAST_CONTACT_DATE 
    FROM RG_AR_COLLECTIONS_LOG
    where customer_id = b.id and entity_id = a.entity_id and invoice_id = a.recv_invoice_id
) c

WHERE a.entity_id IN ('10')
AND a.related_currency_id = 'USD'

Open in new window



If you know there will only be 0 or 1 records in the third table that match the join criteria, then this is more efficient:

SELECT 
b.name AS 'CUSTOMER NAME',
a.journal_customer_id AS 'CUSTOMER ID',
a.entity_id AS 'ENTITY ID',
a.recv_invoice_id AS 'INVOICE ID',
a.invoice_date AS 'INVOICE DATE',
a.amount_15 AS 'AMOUNT',
c.COMMENTS AS 'REMARKS',       
c.LAST_CONTACT_DATE AS 'LAST CONTACT DATE' 

FROM RG_AR_COLLECTION a

INNER JOIN RECEIVABLES_CUSTOMER b
ON b.id = a.journal_customer_id

LEFT OUTER JOIN RG_AR_COLLECTIONS_LOG c
    on c.customer_id = b.id 
    and c.entity_id = a.entity_id 
    and c.invoice_id = a.recv_invoice_id

WHERE a.entity_id IN ('10')
AND a.related_currency_id = 'USD'

Open in new window

Hamed NasrRetired IT Professional
Commented:
Look at first query as one entity: firstEntity
Third table as secondEntity

SELECT a.f1, a.f2, b.f1, b.f2 from firstEntity a
LEFT OUTER JOIN secondEntity b ON a.f1=b.f1 AND a.f2=b.f2
GROUP BY a.f1, a.f2, b.f1, b.f2;

LEFT OUTER JOIN adds null column values from secondEntity where no match in firstEntity exists.
Group by removes duplicates.

Author

Commented:
To AngryBinary,

Thanks for the query.

When I run the new query, It seems I get the right results, but since some invoices could have several remarks, it looks as I am getting duplicates, but I am not based on the last contact date.


I have a couple of questions...

Is there a way that when running the query on SQL Server I can merge all comments of an invoice into only one row?

Eventually I am going to run this query from a Crystal report.

Is there an 'easier' way in Crystal that I can merge the rows into only one column that you know of? - I am aware that might be a question that pertains to a different zone.


At any rate, thanks for your help.

Author

Commented:
AngryBinary the first query that you posted gives me error:

Incorrect syntax near the keyword 'WHERE'.

Pointing to:

WHERE a.entity_id IN ('10')
It may be that the outer join was missing a "ON" statement...

SELECT 
b.name AS 'CUSTOMER NAME',
a.journal_customer_id AS 'CUSTOMER ID',
a.entity_id AS 'ENTITY ID',
a.recv_invoice_id AS 'INVOICE ID',
a.invoice_date AS 'INVOICE DATE',
a.amount_15 AS 'AMOUNT',
c.COMMENTS AS 'REMARKS',       
c.LAST_CONTACT_DATE AS 'LAST CONTACT DATE' 

FROM RG_AR_COLLECTION a

INNER JOIN RECEIVABLES_CUSTOMER b
ON b.id = a.journal_customer_id

LEFT OUTER JOIN (
    select top 1
    COMMENTS,       
    LAST_CONTACT_DATE,
	customer_id,
	entity_id,
	invoice_id
    FROM RG_AR_COLLECTIONS_LOG
    where customer_id = b.id and entity_id = a.entity_id and invoice_id = a.recv_invoice_id
) c
ON c.customer_id = b.id and c.entity_id = a.entity_id and c.invoice_id = a.recv_invoice_id

WHERE a.entity_id IN ('10')
AND a.related_currency_id = 'USD'

Open in new window


To concatenate string values in SQL Server 2000, I think you would need to write a stored proc. You can do it in 2005 or later using the "XML PATH" method as demonstrated here:

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT
  [ID]
, STUFF((SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH ('')),1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

But, this does not work in SQL Server 2000, so I assume it won't work for you.

Honestly, if you want all the comments in all the rows, then you should probably go ahead and get them in separate rows. SQL Server (or database in general) is oriented around data retrieval, not data processing. If the purpose of concatenating is to get a unique row count or some other sort of aggregate, there are other ways to achieve that. Otherwise, processing is better suited to the application layer.