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.CustomerName, Orders.State
for orders which are not in the "Negotiating" or "Delivered" state.

I picked up the "first" keyword from thread:
http://bytes.com/groups/ms-access/19...nstance-record
And tried something like this:

Expand|Select|Wrap|Line Numbers

 
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.CustomerName) 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:

Expand|Select|Wrap|Line Numbers

 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.

Expand|Select|Wrap|Line Numbers

 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...

Expand|Select|Wrap|Line Numbers

 SELECT DISTINCT CustomerName as MyCustName, OrderId, State from Orders LEFT JOIN Customers USING(CustomerNumber) 

where State!='Negotiating' and State!='Delivered'

order by OrderId;

How to find first instance from another table

re: How to find first instance from another table

re: How to find first instance from another table

re: How to find first instance from another table

re: How to find first instance from another table

re: How to find first instance from another table

Similar MySQL Database bytes

/bytes/development

/bytes/it

/bytes/about