Simple Query doesn't work 1

[andy98]

Hi

I am new to mySQL and PHP but have a simplified DB schema like thus:

Customer Table: cust_id, cust_name

Order Table: order_id, cust_id

I have a customer in the customer table -> cust_id=1
And I have an order in the Order table with cust_id = 1

I am just trying to join these two tables together as simply as I can but keep getting the following error:

>>>
You have an error in your SQL syntax near 'order WHERE customer.cust_id = order.cust_id AND customer.cust_id = '1'' at line 1
<<<

My full query is:
>>>
SELECT cust_id, cust_name, rainbow_order_id FROM customer, order
WHERE customer.cust_id = order.cust_id
AND customer.cust_id = '1'
<<<

I can't see why this doesn't work!
As I have said previously - I am new to mySQL and I can't see a way to configure the DB to create the relationships to each table as you would normally do in MS-Sql Server and MS Access. Am I missing something?

Regards

Andy

 

[danomac]

You need to research joining tables, as that method won't work.

Something like:

SELECT customer.cust_id, customer.cust_name, <table>.rainbow_order_id FROM order LEFT JOIN customer ON customer.cust_id=order.cust_id, WHERE customer.cust_id = '1'

Note that you may have to specify the table for rainbow_order_id.

 

[NightGhos]

Try renaming your table to another name like OrderCust because you are using a reserved word in the SQL that is ORDER if the interpreter see that is trying to find a field to generate an asc or desc order and the syntax is bad.

Like is said rename your table and try again.

===================================
Never underestimate the power of stupid people in big numbers
===================================

 

[danomac]

Oops. I totally missed that. Also, the ',' before the WHERE clause in that example query shouldn't be there.

 

[jianbo]

Besides rename table, you can try like this:
SELECT cust_id, cust_name, rainbow_order_id FROM [customer], [order]
WHERE customer.cust_id = order.cust_id
AND customer.cust_id = '1'

 

[sleipnir214]

I don't think the square brackets work with MySQL. Try backticks.

However, use of reserved words as column names is a generally bad idea, regardless of the database system.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[andy98]

NightGhos was correct - Thanks for your help!

I renamed my ORDER table to ACC_ORDER and retried this query:

SELECT * FROM customer, acc_order
WHERE customer.cust_id = acc_order.cust_id AND customer.cust_id = '1'

P.S danomac
You can perform your JOINS in the WHERE clause as the above query works!

 

[Olavxxx]

it's referred to as natural joins.

SELECT * from tbl1, tbl2 WHERE tbl1.field = tbl2.field;

I usually call my fields something that is logical to the table that owns the field..

[User]
usr_id
usr_nam
usr_pass
usr_logged_in

[Images]
img_id
img_owner_id_img_id
img_path

SELECT * FROM `User`, `Images` WHERE `img_owner_id_img_id` = `usr_id` ORDER BY `usr_logged_in` DESC LIMIT 0, 5;

I think it's then easier to "debug" the query, if the fieldnames have something that make them logical kids from theire table parents.

Might just be some bad habbits I have, but I think they are good

Olav Alexander Mjelde

http://www.volvo-power.net

Admin & Webmaster