2 SQL Server queries throw an error I cannot take mssql_fetch_array

Question

I need to take logged users last 200 rows OrderId's from Orders table

WHERE UserName = '$user' 
  AND OrderState = '1' 
  AND OrderId = OrderDetails

My tables here

Orders

OrderId int
UserName nvarchar(64)
OrderState int

OrderDetails

OrderId int
OrderDetailId int

My sample code:

$queryf = "SELECT TOP 200 * FROM Orders WHERE UserName='$user' AND OrderState='1' AND (SELECT * FROM OrderDetails WHERE OrderId='HERE PROBLEM') ORDER BY OrderId DESC";
$resultf = @mssql_query($queryf);
$sayif = @mssql_num_rows($resultf);

while($rowf = @mssql_fetch_array($resultf))
{
   $CustomerIds = $rowf["CustomerId"];
}

Another sample codes added from @vkp

    SELECT TOP 200 o.*
    FROM Orders o 
    JOIN OrderDetails d on o.OrderId = d.OrderId
    WHERE UserName='$user' 
      AND OrderState='1' 
    ORDER BY o.OrderId DESC

Throws an error

[FreeTDS][SQL Server]Ambiguous column name 'UserName'.

I'm trying but I don't know how to resolve it.

Thanks !

@vkp gives SQL Server]Ambiguous column name 'UserName'. error — SwiftDeveloper, Dec 16 '15 at 18:41
Possible duplicate of [1052: Column 'id' in field list is ambiguous](http://stackoverflow.com/questions/6638520/1052-column-id-in-field-list-is-ambiguous) — Sheepy, Dec 17 '15 at 06:08

Siyual · Accepted Answer · 2015-12-16T19:26:11.530

1

If I understand your question correctly, this should be all you need:

Select  Distinct Top 200 O.*
From    Orders          O
Join    OrderDetails    D   On  O.OrderId = D.OrderId
Where   O.UserName = '$user'
And     O.OrderState = 1
Order By O.OrderId Desc

Another option is to use EXISTS:

Select  Top 200 O.*
From    Orders  O
Where   Exists
(
    Select  *
    From    OrderDetails    D
    Where   D.OrderId = O.OrderId
)
And     O.UserName = '$user'
And     O.OrderState = 1
Order By O.OrderId Desc

edited Dec 16 '15 at 19:26

answered Dec 16 '15 at 19:08

Siyual

16,415
8
44
58

dude i checked now coming 4 rows with that OrderIds (1062293,1062293,1062293,1062295) but must be only 2 row (1062293,1062295) – SwiftDeveloper Dec 16 '15 at 19:16
I had a typo in the query that I just corrected a few seconds ago - try the updated query in my answer. – Siyual Dec 16 '15 at 19:17
yes same gives 4 rows must be different 2 rows only different OrderIds – SwiftDeveloper Dec 16 '15 at 19:19
Can you add some sample data and expected results to the question? – Siyual Dec 16 '15 at 19:20
Order ID = 1062293,1062289,1062289,1062289 OrderState = 1,1,1,1 UserName = ok,ok,ok,ok – SwiftDeveloper Dec 16 '15 at 19:22
Oh, hold on, I see the issue. Try again with this query – Siyual Dec 16 '15 at 19:22
gives SQL Server]The image data type cannot be selected as DISTINCT because it is not comparable. – SwiftDeveloper Dec 16 '15 at 19:24
Try the second query - that one should work without a `DISTINCT`. – Siyual Dec 16 '15 at 19:26
1

Thanks man ! Very good ty a lot i think be help too many people – SwiftDeveloper Dec 16 '15 at 19:27

2 SQL Server queries throw an error I cannot take mssql_fetch_array

1 Answers1