0

I have 2 tables:

1) An Order table (which has OrderNumber as the Id)

2) An Items table which can have multiple rows and also has an OrderNumber column tying each row to the ord er row above.

Most orders have multiple rows in the Items table - but some do not and I need to pull a report of Orders with no associated Items rows.

I could do this on 2 queries in my PHP but there is clearly a smarter way to do it in MySQL. I understand JOINS but usually that's when there IS data in both tables. How do I tackle this if there ISN'T a tie in both?

Improve this question
1
  • you want to get the records from the order table with no match in the items table? Commented May 14, 2013 at 11:26

4 Answers 4

Reset to default
3

I would use a LEFT OUTER JOIN or FULL OUTER JOIN, depending on what you need.. It would help if you post the 2 queries, or some sample data...

OUTER handles any data that isn't there

here is a link to help you out: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

SELECT * FROM Order LEFT OUTER JOIN Items ON Order.OrderNumber = Items.OrderNumber
Thanks for the guide and the snippet. Worked well and taught me more than just a copy and paste which I usually do on here (lazy as it is).
1

Let's assume your Items table primary key is Items_Id

SELECT * FROM Orders LEFT JOIN Items USING ( `OrderNumber` ) where Items.Items_Id IS NULL
Improve this answer

Comments

1

In addition to the ways listed above, I'd say that I'd use this structure:

SELECT * FROM Order where not exists (SELECT NULL FROM Items where OrderNumber = Orders.OrderNumber);

You can also add an index on OrderNumber column in Items for better performance:

CREATE INDEX Idx_Items on Items (OrderNumber);
Improve this answer

3 Comments

joins are faster than subqueries. Database 101.
Thank you! The Create Index query was essential as my system bogged down. Cheers!
Yes, joins are faster, but firstly not always, and secondly they're not available sometimes (in dynamic sql for example when you need to add some dynamic filters).
0

You could use a the NOT IN phrase for the where clause. This would get Order records who's Id's aren't in the Items table.

SELECT * FROM Order WHERE Id NOT IN (SELECT OrderNumber FROM Items)
Improve this answer

5 Comments

joins are faster than subqueries. Database 101.
True, but the goal is to get the result set of what orders don't have items, not what are the records of orders and the items that aren't theirs. Logic 101. A LEFT OUTER JOIN will give you a much larger data set to work with, which would then require a where condition to cull through. Sometimes a readable solution, is more appropriate, especially for SQL beginners.
your claim about a "much larger data set" is false, because the output is the same with the proper WHERE restrictions. Also, the subquery will be most probably converted in a temp table and evaluated once for every Order entry, and believe me THIS will yeld a much dataset to extract informations from. Is this more readable? true. Is this a good suggestion for a beginner? nope.
I agree about the inefficiency of Sub-queries. Left Outer Join with the Where clause is the correct approach. I wrote a less efficient query for the sake of a beginner, and the temp table evaluation is something I want to investigate. Mostly, I wish your first comment was as constructive as your second.
I as well. Apologies. Thanks for the tmp table info, apparently this can be used for serious performance tuning.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.