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
 General SQL Server Forums
 New to SQL Server Programming
 Select from 3 tables

Author  Topic 

PeteLeHoq
Starting Member

37 Posts

Posted - 2013-09-30 : 09:21:40
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

52326 Posts

Posted - 2013-09-30 : 10:10:02
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

37 Posts

Posted - 2013-10-02 : 09:36:24
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

982 Posts

Posted - 2013-10-03 : 04:37:07
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

37 Posts

Posted - 2013-10-03 : 09:16:45
Perfect thanks, I see you have to put the LEFT JOIN statements, following each other.
Go to Top of Page
   

- Advertisement -