Mysql

The error is within this statement:

(SELECT name FROM seller WHERE date= '" + reportDate + "' FROM sales WHERE sel=1)

Select allOpen in new window

As you see you used FROM twice. FROM seller, and then FROM sales.
It is unclear what you have tried to achieve by this. I assume it is just a cut-n-paste error.
Most likely this line should be:

(SELECT name FROM seller WHERE date= '" + reportDate + "' )

Select allOpen in new window

or

(SELECT name FROM seller WHERE date= '" + reportDate + "' AND sel=1)

Select allOpen in new window

You will see the same line further when you are retrieving data from trans.

FROM trans WHERE date= '" + reportDate + "' FROM sales WHERE sel=1

Select allOpen in new window

This is also incorrect. Please correct it accordingly.
On a side note, surround the columns that are reserved words with `
In your statement column with the name of "date" should be written in SQL as:

`date`

Select allOpen in new window

refer this

[Link Deleted]

---

The terms of EE do not allow the posting of links to competing sites. Please have a read through the guidelines:

http://support.experts-exchange.com/customer/portal/articles/1162518-using-off-site-content-on-experts-exchange

Thanks for your understanding

ChrisStanyon - Topic Advisor

@pratima_mcs: please be careful with the answers like this. When I posted one like this it was deleted as a "some kind of link" message. Basically, there is a rule somewhere that the answer should be a normal answer, not just a blind link with an answer from another website

I agree with chaau...make sure you review all 3 lines to the "as NAME" to make sure that part is correct.  try executing just that select to make sure you get what you intended for NAME.

Thanks PortletPaul.  What a helpful response.  I will  be back where I can work this out tomorrow and will respond.

PortletPaul - I am trying to avoid empty strings.  I typed the command in at the mysql prompt and upon hitting the return key I simply got the > prompt with no other info from mysql.

This is the string and the result

mysql> SELECT sellerID, head, descrip, ROUND(weight/head) as avg_wgt, weight, price, seller.name, trans.type as sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans." + 'date' = '  + reportDate + ' ORDER BY name ASC, sexcode DESC;
    ">

PortletPaul - I see an error in the line I posted above.  There is a double quote in the trans.+'date part of the command and I removed it and tried it again this time there was an error.

mysql> SELECT sellerID, head, descrip, ROUND(weight/head) as avg_wgt, weight, price, seller.name, trans.type as sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans. + 'date' = '  + reportDate + ' ORDER BY name ASC, sexcode DESC;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+ 'date' = '  + reportDate + ' ORDER BY name ASC, sexcode DESC' at line 1
mysql>

Well, here is some more stuff.  I have changed the command to read as follows.  This is the command and the error now.


mysql> SELECT sellerID, head, descrip, ROUND(weight/head) as avg_wgt, weight, price, seller.name, trans.type as sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans.date + 'reportDate' + ' ORDER BY name ASC, sexcode DESC';
ERROR 1052 (23000): Column 'head' in field list is ambiguous
mysql>


I am attaching a file with a description of the trans table;
transTable

Boy I hope I am not throwing everything into overload hear but I think I have the command properly assigned to a string now.  As you can see in last line of the following code I print the string so I can be sure it is what I want.  I have put the result below the string assignment for you to see.


        String command =
                "SELECT "
                + "SELLERID, HEAD, DESCRIP, "
                + "ROUND(WEIGHT/HEAD) AS AVG_WGT, "
                + "WEIGHT, PRICE, "
                + "seller.name, "
                + "trans.type AS sexcode "
                + "FROM trans "
                + "left join seller on trans.sellerid = seller.number "
                + "WHERE trans.date= '" + reportDate + "' "
                + "ORDER BY NAME ASC, sexcode DESC";
System.out.println(command);  

 SELECT SELLERID, HEAD, DESCRIP, ROUND(WEIGHT/HEAD) AS AVG_WGT, WEIGHT, PRICE, seller.name, trans.type AS sexcode FROM trans left join seller on trans.sellerid = seller.number WHERE trans.date= '2013-04-11' ORDER BY NAME ASC, sexcode DESC;

ERROR 1052 (23000): Column 'HEAD' in field list is ambiguous

>>ERROR 1052 (23000): Column 'HEAD' in field list is ambiguous
both the tables `trans` and `seller` contain the field `head`

so, you need to specify which one you want:

select ...,`trans`.`head`

or

select ...,`seller`.`head`

use of backticks (or not) is up to you.

Great!  This is what ended up with and it works except I can't get the date from trans.  I get an error stating there is not such a column as date.  The rs must be referring to the seller table and the date is in the trans table.  If I can get an idea at to how to get date from the trans table I'm off and running.  What do you think?  Thanks!

       
       
        String command =
                "SELECT "
                + "t.sellerID, t.HEAD, t.DESCRIP, "
                + "ROUND(t.WEIGHT/t.HEAD) AS AVG_WGT, "
                + "t.WEIGHT, t.PRICE, "
                + "s.name, "
                + "t.type AS sexcode "
                + "FROM trans AS t "
                + "LEFT JOIN seller AS s ON t.sellerid = s.number "
                + "WHERE t.date='" + reportDate + "' "
                + "ORDER BY NAME ASC, sexcode DESC";
       
        try {
            st = (Statement) con.createStatement();
            rs = st.executeQuery(command);

       rs.next();
       System.out.println(rs.getDate("date"));

A little on the slow side but I got it.  After looking in the code in my previous post I see I did not have t.date above the FROM in the command string.  I put it in and the problem went away.  My thanks to you for hanging in there.  I learned a lot here.  again THANKS!

FIRST CLASS HELP>  THANKS!!

You are welcome. Thanks for the grading. Cheers, Paul