I have 3 tables I am working with.
TableA, TableB, TableC
TableA contains the information for the client, each row contains all the information for one specific person.
TableB contains information for a message a person in TableA created.
ID DistID Subject Message CreatedDate StatusID SentTotal
57 3 MonList MonList 2012-03-19 16:21:36.117 2 4
TableC contains a list of people who have access to the message in TableB.
ID MessageID DistID StatusID Important OriginalMessagelID
25111 58 516 1 0 NULL
25112 58 519 1 0 NULL
25114 58 374 1 0 NULL
25115 58 5545 1 0 NULL
25116 58 23435 1 0 NULL
TableC MessageID will be the same as TableB ID.
TableB and TableC DistID will reference the DistID in TableA (table A included way to much info to copy for even a single row, it is a 1 to 1 ratio, 1 row for one customer).
I need to produce a query that links these three tables together. Here is what I have so far:
SELECT *
FROM Distributor d
INNER JOIN Messages ms
ON ms.DistID = d.DistID
INNER JOIN Message m
ON m.DistID = d.DistID
WHERE ((d.DCLoginDate <= (GETDATE() - 3) OR d.DCLoginDate IS NULL) AND (CONVERT(VARCHAR(8), m.CreatedDate, 1)) = (CONVERT(VARCHAR(8), GETDATE(), 1)))
Now TableB can have multiple messages created by the same customer, so their DistID can be present in this table more than 1 time.
TableC is the same way, the DistID can be present in this table more than one time.
I started making individual queries that worked:
SELECT *
FROM Distributor
WHERE (DCLoginDate <= (GETDATE() - 3) OR DCLoginDate IS NULL)
SELECT *
FROM Message m
INNER JOIN Messages ms
ON ms.MessageID = m.ID
WHERE ((CONVERT(VARCHAR(8), CreatedDate, 1)) = (CONVERT(VARCHAR(8), GETDATE(), 1)))
The above work correctly, when I tried to combine them into one statement, it started returning 0 results when i should have returned two (from my live data, not the data provided above).
Anyone understand how to get this to work effeciently?
Your join conditions were different between the single query and the individual queries... What about this?
SELECT *
FROM Distributor d
INNER JOIN Messages ms
ON ms.DistID = d.DistID
INNER JOIN Message m
ON m.ID = ms.MessageID
WHERE
((d.DCLoginDate <= (GETDATE() - 3) OR d.DCLoginDate IS NULL) AND
(CONVERT(VARCHAR(8), m.CreatedDate, 1)) = (CONVERT(VARCHAR(8), GETDATE(), 1)))