SQL Select from multiple tables while matching values

Question

I'm trying to use the SQL statement below, to retrieve data from 3 tables. I get a syntax error near [aren1002] however.

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). I'm not totally sure how to structure the statement.

Raj More · Accepted Answer · 2013-10-02 15:52:51Z

3

The columns you are selecting need to be put right after the SELECT portion of your query, then you put the FROM clause next, along with any join criteria. After that, you can put your WHERE clause.

select
holiday_notes.*,
HOLIDAY_REF.holiday_name as holiday_name,
[aren1002].[lookup].lookup_desc,
[aren1002].[lookup].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

edited Oct 2, 2013 at 15:52

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

answered Oct 2, 2013 at 15:38

dcp

55.7k24 gold badges149 silver badges169 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chris L · Accepted Answer · 2013-10-02 15:44:59Z

1

You have an extra FROM in there.

Try this

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

A select statement will normally follow the pattern of: SELECT required fields FROM table (optionally) JOIN join statements to other tables WHERE conditions

edited Oct 2, 2013 at 15:44

answered Oct 2, 2013 at 15:39

Chris L

2,2921 gold badge19 silver badges34 bronze badges

Comments

James · Accepted Answer · 2013-10-02 15:47:00Z

0

I'm no sql guru, and I'm not at a system where I can set up a test quickly, so this may be completely and totally wrong.

But I'm pretty sure you should do all your SELECTs first, then specify the FROM and JOINs.

So I think you want something along the lines of:

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=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

answered Oct 2, 2013 at 15:47

James

3752 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL Select from multiple tables while matching values

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related