Query Help 4

[VonFranzken]

I want this query to show the unique orders per persons firstname, which in this case happens to be the full name
I have 3 tables Users, Orders, OrderDetails.

SELECT DISTINCT DetailOrderID, UserID, OrderUserID, DetailProductName, DetailQuantity, DetailPrice, OrderShipping, OrderTotal, OrderDate, Firstname
FROM Orders o, OrderDetails d, Users u
WHERE rderUserID = u.UserID

Problem with this query is it shows everything like multiple times.

 

[gmmastros]

Looks like you are missing a join on the Orders and OrderDetails table.

SELECT DISTINCT DetailOrderID, UserID,  OrderUserID, DetailProductName, DetailQuantity, DetailPrice, OrderShipping, OrderTotal, OrderDate, Firstname
FROM Orders o, OrderDetails d, Users u
WHERE o.OrderUserID = u.UserID and [red]O.IdField = d.IdField[/red]

I don't know what the field names are, but you should.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[amrita418]

Is that your whole query? If yes then you are missing a join condition between orders and orderdetails hence you see multiple values.

If it was just a cut and paste problem, then what are you criteria to pick up one orderID per firstname??

Regards,
AA

 

[VonFranzken]

Getting closer, but now when I test this I get type mismatch in expression???

SELECT DISTINCT DetailOrderID, OrderReferenceID, UserID, OrderUserID, DetailProductName, DetailQuantity, DetailPrice, OrderShipping, OrderTotal, OrderDate, Firstname
FROM Orders o, OrderDetails d, Users u
WHERE rderUserID = u.UserID and O.OrderReferenceID = d.DetailOrderID

 

[DotNetGnat]

you may need to use Cast() or Convert() functions in your where conditions...

-DNG

 

[VonFranzken]

Not sure I understand in what capacity?

 

[amrita418]

You need to make sure the datatypes on these two columns are the same.

> O.OrderReferenceID = d.DetailOrderID

If not use the cast/convert functions.

 

[DotNetGnat]

something like

Convert(Varchar,rderUserID) = u.UserID

thats is to make sure you have same data types on either side of the = sign

-DNG

 

[gmmastros]

Try running this query.

Select table_name, column_name, data_type From Information_Schema.Columns
Where (TABLE_NAME = 'Orders' And COLUMN_NAME = 'OrderUserId')
       Or (TABLE_NAME = 'Orders' And COLUMN_NAME = 'OrderReferenceId')
       Or (TABLE_NAME = 'Users' And COLUMN_NAME = 'UserId')
       Or (TABLE_NAME = 'OrderDetails' And COLUMN_NAME = 'DetailOrderId')

paste the results in to a post here. It should highlight the data type issue for us.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[VonFranzken]

Ok your right OrderReferenceID is text and DetailOrderID is number so now how would I need to write this:

SELECT DISTINCT DetailOrderID, OrderReferenceID, UserID, OrderUserID, DetailProductName, DetailQuantity, DetailPrice, OrderShipping, OrderTotal, OrderDate, Firstname
FROM Orders o, OrderDetails d, Users u
WHERE rderUserID = u.UserID and O.OrderReferenceID = d.DetailOrderID

 

[DotNetGnat]

try:

O.OrderReferenceID = Convert(varchar,d.DetailOrderID)

-DNG

 

[jbenson001]

WHERE rderUserID = u.UserID and Convert(int, O.OrderReferenceID) = d.DetailOrderID

 

[gmmastros]

jbenson001,

You cannot convert a text field to an int field. You get the following error: Explicit conversion from data type text to int is not allowed.

VonFranzken,
I strongly urge you to change the data type of the field. If this 'text' field is only used to store numbers, then you should change it to an integer field. It may not be fun right now, but in the long run, you'll be glad you did.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jbenson001]

IF the text is a valid numeric value it will convert. I tried it with no errors.