CODEWITHSUNDEEP
sqlsql-server
PeteTheGreek
PeteTheGreek

Reputation: 729

SQL Select from multiple tables while matching values

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.

Upvotes: 0

Views: 74

Answers (3)

dcp
dcp

Reputation: 55467

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

Upvotes: 3

James
James

Reputation: 375

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

Upvotes: 0

Chris L
Chris L

Reputation: 2292

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

Upvotes: 1

Related Questions