Multiple Joins

[kadriirdak]

Hi Guys,

Can anybody spot the error on this code?
it gives me error: SQL Server Database Error: Incorrect syntax near the keyword 'inner'. 5 0

Select e.event_number,e.start_date ,event_desc,  venue_desc,s.location
from events e
inner join venues v on v.venue=e.venue
inner join sessions s on s.event_number=e.event_number and session_type='0'  
(inner join contact_categories cc on left(cc.notes,4)= left(e.event_reference,4) and getdate() between cc.valid_from and cc.valid_to)
where start_date>getdate() and booking='Y'
and number_of_attendees<maximum_attendees+maximum_on_waiting_list
(and cc.contact_number=1588611)

Thank You

[smantscheff]

The ON clause is missing after the first INNER JOIN clause.

[kadriirdak]

Sorry did not get you - where to add the on clause exactly?

[smantscheff]

Sorry, my mistake - a reading error.
Your error would have to be at the bracket of the last inner join. Drop the outermost brackets of line 5.

[kadriirdak]

Ok

1st change - When I do that

SELECT e.event_number,e.start_date ,event_desc,  venue_desc,s.location
FROM events e
INNER JOIN venues v on v.venue=e.venue
INNER JOIN sessions s on s.event_number=e.event_number AND session_type='0'  
INNER JOIN contact_categories cc on LEFT(cc.notes,4)= LEFT(e.event_reference,4) AND getdate() BETWEEN cc.valid_from AND cc.valid_to
WHERE start_date>getdate() AND booking='Y'
AND number_of_attendees<maximum_attendees+maximum_on_waiting_list
(AND cc.contact_number=1588611)

gives me: Error: Incorrect syntax near the keyword 'AND'. 8 0

2nd change - When I do that

SELECT e.event_number,e.start_date ,event_desc,  venue_desc,s.location
FROM events e
INNER JOIN venues v on v.venue=e.venue
INNER JOIN sessions s on s.event_number=e.event_number AND session_type='0'  
INNER JOIN contact_categories cc on LEFT(cc.notes,4)= LEFT(e.event_reference,4) AND getdate() BETWEEN cc.valid_from AND cc.valid_to
WHERE start_date>getdate() AND booking='Y'
AND number_of_attendees<maximum_attendees+maximum_on_waiting_list
AND cc.contact_number=1588611

gives me this: Error: Argument data type text is invalid for argument 1 of left function. 1 0

[smantscheff]

This error message says it plainly enough. You cannot use LEFT on TEXT fields.
By the way, I think it's also bad practice to join on substrings. You cannot have a foreign key constraint on those expressions, which means that your database is potentially corruptible by invalid data.

[kadriirdak]

Oh ok!
Make sense now thank you so much

Do you think if I can take the text field somehow? or I need to find alternative way(different field maybe...)

[smantscheff]

You can add a field for table linking, 4 characters wide, which is fed by a trigger on insert and update. On each update you set it to substr(cc.notes,1,4). Then you can link the tables directly on those fields.

[kadriirdak]

Ok very helpful
Thank you smantscheff!