SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select from 3 tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

PeteLeHoq
Starting Member

35 Posts

Posted - 09/30/2013 :  09:21:40  Show Profile  Reply with Quote
I'm using this statement to get info from 2 tables:

select holiday_notes.*, lookup.lookup_desc as type_desc from holiday_notes left join lookup on holiday_notes.[type]=lookup.lookup_id
where holiday_id=@holiday_id and delete_date is null order by create_date desc

It uses a specific id (holiday_id) to get the notes for that id.

What I need is all records from holiday_notes table, then the type (from holiday_id table) and also the holiday_name from Holiday_Ref table (which uses the holiday_id from holiday_notes table).

I guess it's another join on the holiday_ref table? right join?

Thanks

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 09/30/2013 :  10:10:02  Show Profile  Reply with Quote
yep...it is . You need to start from holiday_id table then do left join with holiday_notes and to holiday_ref on the required relation

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

PeteLeHoq
Starting Member

35 Posts

Posted - 10/02/2013 :  09:36:24  Show Profile  Reply with Quote
I've got this statement so far but I get a syntax error near [aren1002].

select
holiday_notes.*,
HOLIDAY_REF.holiday_name as holiday_name from [aren1002].[HOLIDAY_NOTES]
left join [aren1002].[HOLIDAY_REF] on holiday_notes.holiday_id=HOLIDAY_REF.holiday_id

[aren1002].[lookup].lookup_desc as type_desc from [aren1002].[HOLIDAY_NOTES]
left join [aren1002].[lookup] on holiday_notes.[type]=lookup.lookup_id

where [HOLIDAY_NOTES].delete_date is null order by [HOLIDAY_NOTES].create_date desc

I'm trying to add the holiday_name column from HOLIDAY_REF and type_desc from the second table (lookup).
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
964 Posts

Posted - 10/03/2013 :  04:37:07  Show Profile  Reply with Quote
i believe it should written like

select
	holiday_notes.*,
	HOLIDAY_REF.holiday_name as holiday_name ,
	[aren1002].[lookup].lookup_desc as type_desc 
from	[aren1002].[HOLIDAY_NOTES]
	left join [aren1002].[HOLIDAY_REF] 
	on holiday_notes.holiday_id=HOLIDAY_REF.holiday_id
	left join [aren1002].[lookup] 
	on holiday_notes.[type]=lookup.lookup_id
where	[HOLIDAY_NOTES].delete_date is null 
order by 
	[HOLIDAY_NOTES].create_date desc
Go to Top of Page

PeteLeHoq
Starting Member

35 Posts

Posted - 10/03/2013 :  09:16:45  Show Profile  Reply with Quote
Perfect thanks, I see you have to put the LEFT JOIN statements, following each other.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000