x

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

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

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

fashraf gravatar image

fashraf
428 13 16 21

(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

fashraf gravatar image

fashraf
428 13 16 21

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
103k 19 21 74

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1853
x123

asked: Jul 31, 2012 at 10:14 AM

Seen: 795 times

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