Help with Inner Join

[jacktripper]

I created this inner join query in SQL server, and it seems to work just fine. But, when I try the same thing in mySQL (duplicate database) it gives me an error. I've never tried join statements with mySQL... any help would be nice! Also, is there any free software out there for building relationships and creating queries for mySQL?

SELECT orders.*, orderscart.*, company.*, accounts.*, company.companyid AS Expr1, orders.ordernum AS Expr2 FROM orderscart INNER JOIN orders ON orderscart.ordernum = orders.ordernum INNER JOIN accounts INNER JOIN company ON accounts.companyid = company.companyid ON orders.companyid = company.companyid WHERE (company.companyid = 'bctcom') AND (orders.ordernum = '8975')

 

[sleipnir214]

I reorganized your query:

SELECT
	orders.*, orderscart.*, company.*, accounts.*, company.companyid AS Expr1, orders.ordernum AS Expr2
FROM
			orderscart
		INNER JOIN
			orders
		ON
			orderscart.ordernum = orders.ordernum
	INNER JOIN
			accounts
		INNER JOIN
			company
		ON
			accounts.companyid = company.companyid
	ON
		orders.companyid = company.companyid
WHERE
	(company.companyid = 'bctcom') AND (orders.ordernum = '8975')

Part of it is that you have an inner join comprised of two inner joins. MySQL can combine joins, but only serially:

SELECT
	orders.*, orderscart.*, company.*, accounts.*, company.companyid AS Expr1, orders.ordernum AS Expr2
FROM
				orderscart
			INNER JOIN
				orders
			ON
				orderscart.ordernum = orders.ordernum
		INNER JOIN
			company
		ON
			orders.companyid = company.companyid
	INNER JOIN
		accounts
	ON
		accounts.companyid = company.companyid
WHERE
	(company.companyid = 'bctcom') AND (orders.ordernum = '8975')

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[jacktripper]

Muchos gracias! It worked just fine. I will have to remember this for future reference.