Join expression not supported.?

Question

NH1 0 Light Poster

14 Years Ago

How would i change this so it will work.
I need to link two tables.
but i get an error that says "Join expression not supported".

case 'N'://Get Jobs
                    //long n = Int64.Parse(Del);
                    SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "[DaySchedule]![Shift Code Monday] AS Monday, " +
                            "[DaySchedule]![Shift Code Tuesday] AS Tuesday , " +
                            "[DaySchedule]![Shift Code Wednesday] AS Wednesday, " +
                            "[DaySchedule]![Shift Code Thursday] AS Thursday, " +
                            "[DaySchedule]![Shift Code Friday] AS Friday, " +
                            "[DaySchedule]![Shift Code Saturday] AS Saturday, " +
                            "[DaySchedule]![Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] FROM Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] " +
                            "WHERE Employee.[Employee Number] = '" + Del + "'";
                    break;

4 Contributors
14 Replies
268 Views
1 Day Discussion Span
Latest Post 14 Years Ago Latest Post by Geekitygeek

PierlucSS 52 Junior Poster

14 Years Ago

You forgot the '=' part after your left join on. You didn't not specify the key you want the field you want to join on both table.

SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

PierlucSS 52 Junior Poster

14 Years Ago

i'm not any expert in sql, however it looks fine

PsychoCoder 6 Light Poster

14 Years Ago

If you're using SQL Server then your statement is wrong, not sire what the use of the ! is for

[DaySchedule]![Shift Code Monday] AS Monday,

That should look like this (if using SQL Server)

[DaySchedule].[Shift Code Monday] AS Monday,

Your entire statement is riddled with those.

Edited 14 Years Ago by PsychoCoder because: n/a

Geekitygeek commented: my thoughts exactly +3

PsychoCoder 6 Light Poster

14 Years Ago

Ok now post your current code so we can see the updated version

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

NH1 0 Light Poster · Answer 1 · 2010-08-19T20:05:51+00:00

You forgot the '=' part after your left join on. You didn't not specify the key you want the field you want to join on both table.
SELECT Customers.FirstName, Customers.LastName, SUM(Sales.SaleAmount) AS SalesPerCustomer
FROM Customers JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.FirstName, Customers.LastName

Does this look right.

case 'N'://Get Jobs
                    //long n = Int64.Parse(Del);
                    long n = Int64.Parse(Del);
                    SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday] AS Monday, " +
                            "DaySchedule![Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule![Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule![Shift Code Thursday] AS Thursday, " +
                            "DaySchedule![Shift Code Friday] AS Friday, " +
                            "DaySchedule![Shift Code Saturday] AS Saturday, " +
                            "DaySchedule![Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number], " +
                            "FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
                            "RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
                            "WHERE (((Rules.[Rule Name])= " + n.ToString() +
                            "GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
                            "DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
                            "DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Week Number]";

NH1 0 Light Poster · Answer 2 · 2010-08-19T20:23:14+00:00

i'm not any expert in sql, however it looks fine

Though now i get an error that says "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect."

NH1 0 Light Poster · Answer 3 · 2010-08-19T22:37:05+00:00

If you're using SQL Server then your statement is wrong, not sire what the use of the ! is for
[DaySchedule]![Shift Code Monday] AS Monday,
That should look like this (if using SQL Server)
[DaySchedule].[Shift Code Monday] AS Monday,
Your entire statement is riddled with those.

I thought i changed those. but those aside i get another error that says " data type mismatch in criteria expression".
but i cant find anything that is wrong with the statement.

NH1 0 Light Poster · Answer 4 · 2010-08-19T22:46:05+00:00

case 'N'://Get Jobs
                SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "DaySchedule.[Shift Code Monday] AS Monday " +
                            "DaySchedule![Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule![Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule![Shift Code Thursday] AS Thursday, " +
                            "DaySchedule![Shift Code Friday] AS Friday, " +
                            "DaySchedule![Shift Code Saturday] AS Saturday, " +
                            "DaySchedule![Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] " +
"FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
"RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
"WHERE Rules.[Rule Name] = '" + Del + "'";
"GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
"DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
"DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Shift Code Sunday], DaySchedule.[Week Number]";
                    break;

PsychoCoder 6 Light Poster · Answer 5 · 2010-08-19T22:50:24+00:00

Ok you still have the ! in your query.

In this line

"WHERE Rules.[Rule Name] = '" + Del + "'"

What data type is Del and what type is Rules.[Rule Name] in your table.

For future reference, you really shouldn't have spaces in your column and table names.

NH1 0 Light Poster · Answer 6 · 2010-08-19T22:52:38+00:00

Ok you still have the ! in your query.
In this line
"WHERE Rules.[Rule Name] = '" + Del + "'"
What data type is Del and what type is Rules.[Rule Name] in your table.
For future reference, you really shouldn't have spaces in your column and table names.

Ha ah, sorry ive been trying to figure this out all day. Got so caught up in it that i forgot within seconds to take them out.

But both Del and Rules.[Rule Name] are strings.

NH1 0 Light Poster · Answer 7 · 2010-08-19T22:59:24+00:00

Okay with this code right here (see below), i get this error. that is with the changes psycho gave me.

The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.

case 'N'://Get Jobs
        SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "DaySchedule.[Shift Code Monday] AS Monday " +
                            "DaySchedule.[Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule.[Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule.[Shift Code Thursday] AS Thursday, " +
                            "DaySchedule.[Shift Code Friday] AS Friday, " +
                            "DaySchedule.[Shift Code Saturday] AS Saturday, " +
                            "DaySchedule.[Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] " +
                            "FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
"RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
"WHERE Rules.[Rule Name] = '" + Del + "'" +
"GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
"DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
"DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Shift Code Sunday], DaySchedule.[Week Number]";
                    break;

PsychoCoder 6 Light Poster · Answer 8 · 2010-08-19T23:01:09+00:00

You're missing a comma at the end of this line

"DaySchedule.[Shift Code Monday] AS Monday " +

It should be

"DaySchedule.[Shift Code Monday] AS Monday, " +

NH1 0 Light Poster · Answer 9 · 2010-08-19T23:06:10+00:00

You're missing a comma at the end of this line
"DaySchedule.[Shift Code Monday] AS Monday " +
It should be
"DaySchedule.[Shift Code Monday] AS Monday, " +

Okay with this code right here (see below), i get this error. that is with the changes you gave me.

i still get "data type mismatch in criteria expression".

case 'N'://Get Jobs
        SEL = "SELECT Employee.[Employee Number], Employee.[Last Name], " +
                            "DaySchedule.[Shift Code Monday] AS Monday, " +
                            "DaySchedule.[Shift Code Tuesday] AS Tuesday, " +
                            "DaySchedule.[Shift Code Wednesday] AS Wednesday, " +
                            "DaySchedule.[Shift Code Thursday] AS Thursday, " +
                            "DaySchedule.[Shift Code Friday] AS Friday, " +
                            "DaySchedule.[Shift Code Saturday] AS Saturday, " +
                            "DaySchedule.[Shift Code Sunday] AS Sunday, " +
                            "DaySchedule.[Week Number] " +
                            "FROM (Employee LEFT JOIN DaySchedule ON Employee.[Employee Number] = DaySchedule.[Employee Number]) " +
"RIGHT JOIN Rules ON Employee.[Default Shift Code] = Rules.[Rule Name] " +
"WHERE Rules.[Rule Name] = '" + Del + "'" +
"GROUP BY Employee.[Employee Number], Employee.[Last Name], DaySchedule![Shift Code Monday], " +
"DaySchedule.[Shift Code Tuesday], DaySchedule.[Shift Code Wednesday], DaySchedule.[Shift Code Thursday], " +
"DaySchedule.[Shift Code Friday], DaySchedule.[Shift Code Saturday], DaySchedule.[Shift Code Sunday], DaySchedule.[Week Number]";
                    break;

Geekitygeek 480 Nearly a Posting Virtuoso · Answer 10 · 2010-08-20T15:26:57+00:00

You still have an '!' in your group by clause: DaySchedule![Shift Code Monday]