Create View

[maes]

Does MySQL support views?
I'm using version : mysql-3.23.38

and this is what I'm trying o do:

CREATE VIEW V_RENTINGS (CUSTOMERID, NAME, SERIALNUMBER, DATUM) AS
SELECT tcustomer.customerID, C.Name, D.Serialnumber, R.d_start
FROM tcustomer C, tDevice D, tdevice_customer R
WHERE D.serialnumber=R.Serialnumber and C.CustomerID=R.customerID


But I allways get an error:
Error: 1064 - You have an error in your SQL syntax near 'VIEW V_RENTINGS (CUSTOMERID, NAME, SERIALNUMBER, DATUM) AS
SELECT tcustomer.cus' at line 1
What am I doing wrong?

And how about subqueries? I hought I read somewhere that MySQL didn't support subqueries. And you should make two queries out of it. but how can I do that?


this is my problem
I have a table with serialnumbers, customerID and d_start

serialnumber|customerid|d_start
1 | 2 | 2003-5-1
2 | 2 | 2003-5-2
3 | 2 | 2003-5-3
4 | 2 | 2003-5-4
1 | 3 | 2003-5-5
2 | 4 | 2003-5-6
5 | 5 | 2003-5-7
2 | 1 | 2003-5-8

I want to select the row with the latest date vor each serialnumber
so I should get
1 | 3| 2003-5-5
2 | 1| 2003-5-8
3 | 2| 2003-5-3
4 | 2| 2003-5-4
5 | 5| 2003-5-7

I tried creating a view and then with a subquery getting the MAX-date and selecting the customerID
but he wont do it,

Anybody any ideas?


Thank you

Maes

[raf]

About the viev:
create view ? Not supported by MySQL as far as i know. Quick search at the MySQL site didn't turn up anything either. Why do you use that ? A view (as far as i know) is some sort of temporarely table the db creates automatically if an sql statement requires this (for instance, if you join tables or use subquerys or ...). Why do you need that? Can't you create a temporarely table?

About the subselects:
MySQL supports subquerys ('subselects') from version 4.1 on.
http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html
(this page tells you more about alternative statements, using joins, for some querys)
In your case, you'de need a distinct subquery with a max agregation function in the first select OR a group by and Max() query. Maybe try
sql="SELECT Max(d_start), serialnumber, customerid FROM table GROUP BY customerid"
http://www.mysql.com/doc/en/Group_by_functions.html

[maes]

Thanks for the quick reply

the oriinal idea was this
first create the view and hen I could use it in my select statements like this:

SELECT A.Name, A. serialnumber, A.d_star
FROM V_RENTINGS A
WHERE A.d_start in (SELECT MAX(B.d_start)
FROM V_RENTINGS B
GROUP By B.Serialnumber)
ORDER By serialnumber


That would give me the disired result. But neither the VIEW or the Subselect works in MySQL :(

Then, I tried your advice. I created the a temporary table with the heap type, but I got an error sayng BLOB/text not allowed in heap tables. the name from tcustomer is a test field. removing it from the temporary table wasn't much of a solution because that would give me back my original tdevice_customer table.

So now I went looking for an otheroption.
I'm now doing through PHP
I first make a select statement that gets all the max(d_start) grouped by serialnumber.

then I walk throough the result with a hile loop.
In that while loop I select the customerid where the serialnumber and d_start equals the value for the record from the MAX(d_start query)
then I select the name from tcustomers where the customerid equals the id from the previeous query


$query="SELECT MAX(d_start), serialnumber
FROM Tdevice_customer
GROUP BY serialnumber";
$date_r=mysql_query($query) or die(mysql_error());

while($sn=mysql_fetch_row($date_r))
{
$query="SELECT customerid
FROM Tdevice_customer
WHERE serialnumber='$sn[1]' and d_start='$sn[0]'";
$custid_r=mysql_query($query) or die(mysql_error());
$customerid=mysql_fetch_row($custid_r);

$query="SELECT name
FROM Tcustomer
WHERE customerid='$customerid[0]'";
$cust_r=mysql_query($query) or die(mysql_error());
$customer=mysql_fetch_row($cust_r);
}

that will give the names of the customers and their serialnumbers

I know it isn't very performant (sp?) but it is the only way I know how :(

[maes]

/*new post because my other one was to long*/

sql="SELECT Max(d_start), serialnumber, customerid FROM table GROUP BY customerid"

gave me the wrong result. customers can have more then one device with a serialnumber. so it displayed only one serianumber for each customer (the serialnumber with the last d_start)

the only problem I stil have with the not very performant code from abouve is that it doesnt sort by customer name. but maybe some php script will take care of that

Thanks alot raf for your quick reply :)

[raf]

About the sorting, just add an order by clause to the statement. Like
$query="SELECT name
FROM Tcustomer
WHERE customerid='$customerid[0]' ORDER BY name asc";
$cust_r=mysql_query($query) or die(mysql_error());
$customer=mysql_fetch_row($cust_r);

about the sql. Ahh. You need the distinct only on the serialnumber to get the record with the max-date.
I don't immedeately see a way to get around that without subquerys.
<edit>
I think you can skip one sql query (ot gat the name) by using a join. Like
sql="SELECT Tdevice_customer.customerid, Tcustomer.name FROM Tdevice_customer INNER JOIN Tcustomer ON Tdevice_customer.customerid = Tcustomer.customerid WHERE serialnumber='$sn[1]' and d_start='$sn[0]' ORDER BY Tcustomer.name"

Also, it would probably be a good idea to have a primary key in the Tcustomer table so you only need the primary key in the where clause</edit>

[maes]

you're right, I think I can do do it that join. (haven't tested it)

>>Also, it would probably be a good idea to have a primary key in the Tcustomer table so you only need the primary key in the where clause

thx, I have a primary key in it (auto nummering) I'll change the WHERE... to match the primary key

I don't think that ORDER BY is going to work because cutsomerid is my primary key, and that statement should only return one name. The poblem is with the firs sql statemen, that is the place where I should be able to sort on the name, but I can't get to that name ithout a sub-query

Thanks Raf, I'll try that join and I'll change the WHERE clause to the primary key :)

[raf]

Hmm. Indeed. The order by wount have an effect snce you'll only have 1 record in that recordset. (didn't read it carefully enough :o)

You could use php (store these names in an array en then sort on it) on store the recordset from the first sql statement into a temporarely table (or insert it in an existing one with select ... into) and then have a three table join query (this table with Tcustomer on the primary key, and Tcustomer with Tdevice_customer on the customer ID) and add the 'order by name' clause at the end of this statement. This will undoubtly be the fastes and easiest

[maes]

I changed the join and the where statement, tomorrow , I'll try what you said about that last join.

Thanks alot for your help, I really apreciate it :thumbsup:

--Maes

[Weirdan]

Originally posted by maes
/*new post because my other one was to long*/

sql="SELECT Max(d_start), serialnumber, customerid FROM table GROUP BY customerid"

gave me the wrong result. customers can have more then one device with a serialnumber. so it displayed only one serianumber for each customer (the serialnumber with the last d_start)



Why don't you do it like this:

sql="SELECT Max(d_start), serialnumber, customerid FROM table GROUP BY customerid,serialnumber"

This will work as needed, I think.

[maes]

that gives me my entire table :(

But thanks anyway :)