SQLTeam.com | Weblogs | Forums

JOIN on Not Equal and Display


#1

I have a two part question. The first is that I am trying to do a join between two tables. I need to select the Order number, Line Item Name, and Line Item Qty and return results on only what is not equal on the Line Item Qty. The second part is that when I select my columns, the column name appears and not the data. How do I get the data to show up?


#2
SELECT t1.*,t2.*
FROM
	Table1 t1
	INNER JOIN Table2 t2 ON
		t2.OrderNumber = t1.OrderNumber
		AND t2.LineItemName = t1.LineItemName
		AND t2.LineItemQuantity <> t1.LineItemQuantity

#3

Thanks for the prompt response. I'm a bit rusty on my SQL. I learned on IBM AS400 and it has been about 10 years since I used it.

Here is what I am getting... "#1064 - 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 '1 t1 INNER JOIN Orders 2 t2 ON t2.OrderNumber = t1.OrderNumber AND t2.LineI' at line 2". My tables are called "Orders 1" and "Orders 2".

SELECT t1.,t2.
FROM Orders 1 t1
INNER JOIN Orders 2 t2 ON
t2.OrderNumber = t1.OrderNumber
AND t2.LineItemName = t1.LineItemName
AND t2.LineItemQuantity <> t1.LineItemQuantity


#4

You got an extra "1" and "2" in the table alias location:

SELECT t1. * , t2. *
FROM Orders t1
INNER JOIN Orders t2 ON
t2.OrderNumber = t1.OrderNumber
AND t2.LineItemName = t1.LineItemName
AND t2.LineItemQuantity <> t1.LineItemQuantity


#5

Scott,

When I do it the way you have it I get this message "#1146 - Table 'srotestb_orders.Orders' doesn't exist".


#6

I have no idea what your table names are / should be, I was basing them off your earlier post. Also, as this is a SQL Server rather than a MySQL forum, I can't provide any exact assistance for MySQL.


#7

SELECT t1.,t2.
FROM [Orders 1] t1
INNER JOIN [Orders 2] t2 ON
t2.OrderNumber = t1.OrderNumber
AND t2.LineItemName = t1.LineItemName
AND t2.LineItemQuantity <> t1.LineItemQuantity