How to find first instance from another table

[paragpdoke]

Hello All.
I am new to SQL ... trying to make a query work untimately in Perl (v5.10.0 built for MSWin32-x86-multi-thread) against MySQL hosted remotely (which currently I don't know the version for). I am currently trying with Quantum DB plugin for Eclipse to run the query.

I am trying to work with 2 tables ... something equivalent to the following example:
Table: Orders
- OrderId (unique integer)
- CustomerNumber (non-unique integer)
- State (string - I guess varchar is the right term)

Table: Customers
- CustomerNumber (integer; unfortunately this isn't unique and I don't have write permissions; so I can only modify my select query)
- CustomerName

I have stated only relevant table and field names.
My intent is to generate output of the form:
Orders.OrderId, Customers.Custo merName, Orders.State
for orders which are not in the "Negotiatin g" or "Delivered" state.

I picked up the "first" keyword from thread:

http://bytes.com/groups/ms-access/190741-find-first-instance-record

And tried something like this:

select first(Customers.CustomerName) as MyCustName, Orders.OrderId, Orders.State
from Orders, Customers
where Orders.State!='Negotiating'
and Orders.State!='Delivered'
and Customers.CustomerNumber=Orders.CustomerNumber
order by OrderId;

But Quantum DB throws an error that the syntax isn't right near (Customers.Cust omerName) on line 1.

Can someone please help me find my mistake ? A link to an existing article will also be fine.

Thanks in advance,
Parag Doke

[SLauren]

You can find it from Mysql command prompt ("help first"), that FIRST keyword can be used with "CREATE TABLE","ALTER TABLE" and "HANDLER".

I am not very sure at this point about the requirements you have, but if you are looking for to retrieve the values as you have specified, then you can execute your query with a little modification like:

select Customers.CustomerName as MyCustName, Orders.OrderId, Orders.State from Orders, Customers where Orders.State!='Negotiating' 
and Orders.State!='Delivered' and Customers.CustomerNumber=Orders.CustomerNumber 
order by OrderId;

If you want to use FIRST then you can do that using ALTER TABLE sentence like:


ALTER TABLE Customers ADD CustomerName VARCHAR(<length >) FIRST;

Thanks,
Lauren

[paragpdoke]

Hello Lauren.
Actually, the query you mentioned is exactly what I first tried.
However, my problem is that there are multiple records in the Customer table with the same CustomerNumber and CustomerName. So the recordset has more records than it actually should have.

Do you know how to make the query select only one CustomerName from the Customers table ?

Thanks in advance,
Parag Doke

[SLauren]

Hi Parag,
You can use DISTINCT keyword while retrieving the data from the tables. But the thing is you might have different orders even if your CustomerNumber and CustomerName are same, so in that you will get multiple records, otherwise if your order ids and order states(??) are same for the same CustomerName and CustomerNumber then you will get single record.

select DISTINCT Customers.CustomerName as MyCustName, Orders.OrderId, Orders.State from Orders, Customers where Orders.State!='Negotiating' 
and Orders.State!='Delivered' and Customers.CustomerNumber=Orders.CustomerNumber 
order by OrderId;

Thanks,
Lauren

[paragpdoke]

Thank you Lauren.
That worked for me. I do not understand the 2 cases you were trying to explain...but I tried the distinct keyword and got my query to return exactly the same records I wanted.

Though the example sounded like a homework exercise, it is actually a work related query. It just happened that I haven't worked on SQL earlier. Your reply got my held up work started again.

Thank you once more.

Regards,
Parag Doke

[daniel2335]

To make your query a little more optimised and tidy you could try this...

SELECT DISTINCT CustomerName as MyCustName, OrderId, State from Orders LEFT JOIN Customers USING(CustomerNumber) 
where State!='Negotiating' and State!='Delivered'
order by OrderId;