need help with query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

need help with query

Author

Topic

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 13:31:04

I am writing a query that returns information regarding whether or not an email was sent to a supplier.
It should return the supplierID, subject and datesent.

I have a supplier table(supplierID), an email table (emailID, subject) and a supplieremail (datesent, supplierID, emailID) table.

What I want to see is for every supplier, show every email subject, and then either the datesent, or NULL if no record exists for that supplierID.

SELECT	s.[supplierID],
		[subject],
		[dateSent]
FROM    Compliance.Email e LEFT JOIN Compliance.SupplierEmail se
		ON se.emailID = e.emailID, Compliance.Supplier s
WHERE s.supplierID = se.supplierID OR se.supplierID IS NULL
ORDER BY supplierID, dateSent DESC

There are 4 email records in the email table, and 19 suppliers in the supplier table, so this should return 76 records, but it only returns 68.

Can someone help me figure out what's wrong with this query?
Thanks
Aaron

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 13:53:38

Could you show us some sample data of rows that are being returned from your query and then a few rows that aren't being returned? We only need to see supplierID, subject, dateSent, and emailID. Make sure that you tell us if the values are different in the child tables as that would impact the results here.

Why are you mixing your JOIN syntax, specifically for Supplier?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 14:07:54

this is what I would like to see:

supplierID, subject, dateSent
3093, Email Subject 1, 2008-12-22 08:47:40.540
3093, Email Subject 2, 2008-12-22 08:47:40.540
3093, Email Subject 3, 2008-12-22 08:47:40.540
3093, Email Subject 4, NULL
3094, Email Subject 1, 2008-12-22 08:47:40.540
3094, Email Subject 2, 2008-12-22 08:47:40.540
3094, Email Subject 3, NULL
3094, Email Subject 4, NULL
3095, Email Subject 1, 2008-12-22 08:47:40.540
3095, Email Subject 2, NULL
3095, Email Subject 3, 2008-12-22 08:47:40.540
3095, Email Subject 4, NULL

but this is what i get:

3093, Email Subject 1, 2008-12-22 08:47:40.540
3093, Email Subject 2, 2008-12-22 08:47:40.540
3093, Email Subject 3, 2008-12-22 08:47:40.540
3093, Email Subject 4, NULL
3094, Email Subject 1, 2008-12-22 08:47:40.540
3094, Email Subject 2, 2008-12-22 08:47:40.540
3095, Email Subject 1, 2008-12-22 08:47:40.540
3095, Email Subject 2, NULL

There are 19 supplier records, 4 email records and 49 supplieremail records. Most suppliers have received 2 or 3 emails, and some have received only one. Where the NULL values are, there are no supplieremail records. I hope this helps

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 14:12:36

Does this return any rows?: SELECT * FROM Compliance.Supplier WHERE supplierID = 3094

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 14:18:04

yes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 14:22:37

Oops I didn't read your data correctly. Let me look at it more closely.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 14:23:31

Show us the data from all 3 tables for supplierID = 3094.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 14:36:39

supplier
supplierID
3094

email
emailID, subject
1, Email Subject 1
2, Email Subject 2
3, Email Subject 3
4, Email Subject 4

SupplierEmail
supplierID, dateSent, emailID
3094, 2008-12-22 08:47:40.540, 1
3094, 2008-12-22 08:47:40.540, 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 14:40:51

How would emailID 3 and 4 be associated to 3094? I don't see any related data for those 2 rows.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 14:52:15

they are not.
That's where i would like to show NULL in datesent for those values without relative records

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 14:55:16

Then you need to restructure your query, give me a few...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 14:58:19

thanks a lot for your help!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 15:04:30

Hmmm, when I run your sample and your query, I get the desired results. Run this in a test environment to see:


CREATE TABLE supplier (supplierID int)
CREATE TABLE email (emailID int, subject varchar(20))
CREATE TABLE SupplierEmail (supplierID int, dateSent datetime, emailID int)

INSERT INTO supplier VALUES(3094)
INSERT INTO email VALUES(1, 'Email Subject 1')
INSERT INTO email VALUES(2, 'Email Subject 2')
INSERT INTO email VALUES(3, 'Email Subject 3')
INSERT INTO email VALUES(4, 'Email Subject 4')
INSERT INTO SupplierEmail VALUES(3094, '2008-12-22 08:47:40.540', 1)
INSERT INTO SupplierEmail VALUES(3094, '2008-12-22 08:47:40.540', 2)

SELECT	s.[supplierID],
		[subject],
		[dateSent]
FROM    Email e LEFT JOIN SupplierEmail se
		ON se.emailID = e.emailID, Supplier s
WHERE s.supplierID = se.supplierID OR se.supplierID IS NULL
ORDER BY supplierID, dateSent DESC

DROP TABLE supplier, email, SupplierEmail

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

abomb
Starting Member

13 Posts

Posted - 2008-12-22 : 15:15:13

weird.
i ran that and it worked fine.
but when i try the same query on my actual tables, it still returns some suppliers with only 2 entries

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-22 : 15:16:30

I can't really help without seeing a better sample.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources