ASP.NET

What is wrong in this SQL question? RSS

8 replies

Last post Jul 29, 2012 10:00 PM by onlycparra

‹ Previous Thread|Next Thread ›

- Reply
onlycparra

Member

2 Points

4 Posts

What is wrong in this SQL question?

Jul 28, 2012 08:45 PM|LINK

I'm working with MSAccess that say me:
[Microsoft][Controller ODBC Microsoft Access];The join expression not supported.

This is the question:
SELECT
productos.codig_producto,
productos.marca,
productos.descripcion,
stock_mensual.prec_costo,
stock_mensual.prec_venta,
stock_mensual.cantid_inicial,
stock_mensual.cantid_actual,
stock_mensual.costo,
stock_mensual.ingreso
FROM productos INNER JOIN stock_mensual
ON productos.codig_producto=stock_mensual.codig_producto
AND codig_producto='123'
AND mes=7
AND anio=2012;

Data types:
String productos.codig_producto
String stock_mensual.codig_producto
int mes
int anio
- Edited by onlycparra on Jul 28, 2012 08:50 PM
- Edited by onlycparra on Jul 29, 2012 12:58 PM
- Edited by onlycparra on Jul 29, 2012 12:58 PM
- Edited by onlycparra on Jul 29, 2012 01:05 PM
- Edited by onlycparra on Jul 29, 2012 01:06 PM
- Edited by onlycparra on Jul 29, 2012 01:06 PM
- Edited by onlycparra on Jul 29, 2012 01:08 PM
- Moved by mbanavige on Jul 29, 2012 11:27 PM
- Edited by onlycparra on Jul 30, 2012 12:31 AM
- Reply
Dan Bracuk

Contributor

3970 Points

1096 Posts

Re: What is wrong in this SQL question?

Jul 28, 2012 08:55 PM|LINK

I don't work with Access but based on the error message, maybe you have to join your tables in the where clause.
- Marked as answer by onlycparra on Jul 29, 2012 10:00 PM
- Reply
Primillo

Star

8723 Points

1677 Posts
Re: What is wrong in this SQL question?

Jul 28, 2012 10:40 PM|LINK
Hi

Try adding the "ORDER BY"
```
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.P_Id=Orders.P_Id
ORDER BY Persons.LastName
```
http://www.w3schools.com/sql/sql_join_inner.asp
Best Regards
Primillo
http://www.facebook.com/programandopuntonet
- Reply
atlumer07

Member

188 Points

61 Posts

Re: What is wrong in this SQL question?

Jul 29, 2012 01:34 AM|LINK

Hi onlycparra,

Try using cartesian instead of usual joins..

maybe this query can help.

SELECT
productos.codig_producto,
productos.marca,
productos.descripcion,
stock_mensual.prec_costo,
stock_mensual.prec_venta,
stock_mensual.cantid_inicial,
stock_mensual.cantid_actual,
stock_mensual.costo,
stock_mensual.ingreso
FROM productos,stock_mensual
Where productos.codig_producto=stock_mensual.codig_producto
AND codig_producto=123
AND mes=7
AND anio=2012;

hope it helps..
- Marked as answer by onlycparra on Jul 29, 2012 10:00 PM
- Reply
santa_1975

Star

8574 Points

1499 Posts

Re: What is wrong in this SQL question?

Jul 29, 2012 05:14 AM|LINK

Hi,

Your query looks okay except that you need to modify the below line,

AND codig_producto='123'

to this (remove quotes) because you've mentioned that "cdig_prducto" is of "int" type.

AND codig_producto=123

Refer to the below link for more information on the INNER JOIN.

http://msdn.microsoft.com/en-us/library/office/bb208854(v=office.12).aspx

Hope this helps.

Please remember to click “Mark as Answer” on the post that helps you and to unmark it if a marked post does not actually answer your question.
Thank you!
----------------------
"Microsoft Community Contributor Award 2011"
- Reply
onlycparra

Member

2 Points

4 Posts

Re: What is wrong in this SQL question?

Jul 29, 2012 12:58 PM|LINK
oh!, I'm sorry, codig_producto is string, not an int, excuse me, it is string, I think it is a problem with the join, maybe a pear of ()... it will become me crazy… thanks for your answers, I try whith a Cartesian product but it give me falses numbers in the other fields.
- Reply
CPrakash82

All-Star

18152 Points

2830 Posts

Re: What is wrong in this SQL question?

Jul 29, 2012 01:37 PM|LINK

onlycparra
Data types: String productos.codig_producto String stock_mensual.codig_producto int mes int anio

I am just wondering, if your both table has correct datatype for the column - codig_producto ? Please verify

http://cprakash.com
- Reply
onlycparra

Member

2 Points

4 Posts

Re: What is wrong in this SQL question?

Jul 29, 2012 08:39 PM|LINK

yes, verified, both are Strings, in access it is "text", does the text size could influence?, maby it is not permitted a long text... something like the "memo" datatype..., I'm going to reduce it and after I tell you about.
- Edited by onlycparra on Jul 29, 2012 08:40 PM
- Reply
onlycparra

Member

2 Points

4 Posts

Re: What is wrong in this SQL question?

Jul 29, 2012 10:00 PM|LINK

Dan Bracuk
I don't work with Access but based on the error message, maybe you have to join your tables in the where clause.

jajaja, I had not understood that, the correct sentence seems to be:

SELECT productos.codig_producto,
productos.marca,
productos.descripcion,
stock_mensual.prec_costo,
stock_mensual.prec_venta,
stock_mensual.cantid_inicial,
stock_mensual.cantid_actual,
stock_mensual.costo,
stock_mensual.ingreso
FROM productos INNER JOIN stock_mensual
ON productos.codig_producto=stock_mensual.codig_producto
WHERE stock_mensual.codig_producto='123'
AND stock_mensual.mes=7
AND stock_mensual.anio=2012;

The problem was that there are not WHERE clause...

And...

atlumer07
Try using cartesian instead of usual joins..

Yes, it really works, the "spurious tuple" was other problem with an assign... plop!

Thanks, really, to all. :)
- Marked as answer by onlycparra on Jul 29, 2012 10:00 PM