Joining Two tables

The First image is my main table "Master"..and the second one is another table "Item"

I the Serno in the master is the Foreign key(Items) in the Item
Now if i use the where condition with Order_Id=38 I keep getting the existing rows..serno 7,9 from the Master but i need to get 8,10

[1]: /storage/temp/329-item.png

main.png (8.1 kB)
item.png (6.8 kB)
more ▼

asked Jul 31, 2012 at 10:14 AM in Default

avatar image

538 17 22 29

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first
  • Method 1 Select * From Menu_Master where serno not in (select items from order_items where order_id = 38)

  • Method 2 select a.serno, a.cat, a.item, a.cost, a.datetime from menu_master a left outer join order_items as b on a.serno = b.items and b.order_id = 38 where b.items is null

more ▼

answered Jul 31, 2012 at 11:36 AM

avatar image

538 17 22 29

(comments are locked)
10|1200 characters needed characters left

If you pass it the Order_ID as 38, from the data you show, SQL Server can only return semo 7 and 9 because those are the only values where the Order_Id matches. Not seeing the complete structure, it's hard to say more. Maybe you're looking for an OUTER JOIN to retrieve all data from one table, and only the matching data from the other. In this case, just make sure that the filtering criteria is in the ON clause, not the WHERE clause.

more ▼

answered Jul 31, 2012 at 10:36 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

Thank you sir for the reply..But that's my issue ..how do i get the columns which have not being used along with the where condition .. because there are loads of order-Id like 38 ,39 ,40

Jul 31, 2012 at 10:45 AM fashraf

I guess I don't understand. If you filter for a particular condition, Order_Id = 38, then you should only expect to see rows that match that condition returned. That's the idea behind applying a filter. If you want to see all data returned, then you can't apply a condition like that.

Jul 31, 2012 at 11:24 AM Grant Fritchey ♦♦

Method 2 should work better generally and is easier to understand.

Jul 31, 2012 at 11:43 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 31, 2012 at 10:14 AM

Seen: 929 times

Last Updated: Jul 31, 2012 at 11:49 AM

Copyright 2018 Redgate Software. Privacy Policy