Query help pls - 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

Transact-SQL (2000)

Query help pls

Author

Topic

ann
Posting Yak Master

220 Posts

Posted - 2006-07-06 : 14:34:27

I have 2 tables, parent = notes, child = notesaddendum which is a one to many.
Notes Table:
noteid, noteauthor, note, notecreateddate

Notesaddendum Table:
NotesaddendumID, noteid, addNote, addAuthor, addcreateddate

what I need to do is join both tables (I'm using left join) which is fine. The part I am having issues with is that I need to be able to sort by date - so the date field from both tables are in chronilogical, somehow make them one field. I have no idea how I would go about this. Can some one help?
Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-06 : 14:40:39

but what does that mean, do you want to use the older or newer data?

something like:

ORDER BY CASE WHEN date1 < date2 THEN date1 ELSE date2 END

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ann
Posting Yak Master

220 Posts

Posted - 2006-07-06 : 14:42:31

I want to use all dates from both table and just sort asc. I just don't understand how I can do that when they are different tables

X002548
Not Just a Number

15586 Posts

Posted - 2006-07-06 : 14:49:17

Can you show us a sample of what you expect the result set to look like? Do you want this to be a union instead of a join?

read the hint link in my sig

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

ann
Posting Yak Master

220 Posts

Posted - 2006-07-06 : 15:02:06

Table 1: noteid (int), noteauthor (varchar 100), note(varchar250), notecreatedate (datetime)
data: 1, user1, blah blah blah, 6/1/2006
data: 2, user2, blah blah blah, 6/15/2006
data: 3, user3, blah blah blah, 6/30/2006

Table 2: NotesaddendumID(int), noteid (int), addauthor(varchar 100),addnote(varchar250),addcreatedate (datetime)
data: 1,1, user1, blah blah blah, 6/15/2006
data: 2,2, user2, blah blah blah, 6/7/2006
data: 3,3, user3, blah blah blah, 6/25/2006

results:
3, user3, blah blah blah, 6/30/2006
3,3, user3, blah blah blah, 6/25/2006
2, user2, blah blah blah, 6/15/2006
1,1, user1, blah blah blah, 6/15/2006
data: 2,2, user2, blah blah blah, 6/7/2006
1, user1, blah blah blah, 6/1/2006

You think I should be doing a union instead of a left join? lol, I've never done that, will that give me the results I want

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-07-06 : 15:48:53

Your output result set shows 4 columns on some rows, and 5 columns on other rows. You have to have the same number of columns on each row.

CODO ERGO SUM

ann
Posting Yak Master

220 Posts

Posted - 2006-07-06 : 17:34:49

sorry bout that - i just copied and pasted:

user3, blah blah blah, 6/30/2006
user3, blah blah blah, 6/25/2006
user2, blah blah blah, 6/15/2006
user1, blah blah blah, 6/15/2006
user2, blah blah blah, 6/7/2006
user1, blah blah blah, 6/1/2006

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-06 : 17:44:52

Here is my go at it:


SELECT Author, DateColumn
FROM 
(
	SELECT noteauthor AS Author, notecreatedate AS DateColumn
	FROM Table1
	UNION ALL
	SELECT addauthor, addcreatedate
	FROM Table2
) t
ORDER BY DateColumn

Just add in the extra columns in the derived table. Make sure to provide a column name for it if the names are different in the tables.

Tara Kizer
aka tduggan

ann
Posting Yak Master

220 Posts

Posted - 2006-07-06 : 17:56:16

Tara - that worked great! Thanks so much all for taking the time to help - I really appreciate it.
I am new to sql other than basic select * from where....
I did read about unions in the sql books on line, but the examples didn't seem to help, rather confused me more ! LOL.
Tara your example was great, I even modified it for more columns that I needed. YOU should be writing the examples for the sql books (at least for the unions :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources