[RESOLVED] Query Problem...

[khandu]

I have a the following in a Database

table1 : order
table2 : ship

order fields : userid,orderid etc...
ship fields : userid,orderid etc..

now i want a query such that.. select all orders from order table where order.userid = 1 and order.orderid = ship.orderid

i did this in VB6 but gave error

select * FROM order WHERE order.userid = 1 and order.orderid = ship.orderid

gives error

please tell me correct statement..

[si_the_geek]

First off, is there a reason for Ship to contain the field UserID? I would assume that OrderID is unique within Order (ie: each OrderID only applies to one UserID), so Ship can simply link on that one field.


As to the query, the problem is that you are trying to work with data that is not in your From clause - you have specified that you only want to work with data that is in the Order table.

There are two ways to do this (either a join or a sub-query), but which you should use depends on what you are trying to achieve... are you trying to get a list of all fields in the Order table where at least one record exists in the Ship table with the same OrderID?

[khandu]

well, see.. I am populating a cmblist with all orderid.. CONDITION

that should contain only those orderid which exist in both order and ship table i.e. all orderid will be in order table but NOT COMPULSORY to be in ship table.. basically if the order is not shipped it will not be there in ship table.. so i only want those orderid which have shipped also..

the userid and orderid both exist in both the table "order" and "ship"

both are unique.. i.e. there cannot be duplicate orderid for same user..

and i only want to populate the list with the userid orders.. which userid is stored in a variable "userid" when he logs in

on top i took "1" just as example

so in the end.. i want to select all orderid of the user ( userid ) logged in which exist in order and ship table

please help

[si_the_geek]

That's a very confusing explanation.. you basically said the OrderID has to be in both tables, but at the same time it doesn't have to be!

If you want all OrderID's that exist in both tables (Order and Ship), you would use this:

select order.orderid FROM order INNER JOIN ship ON order.orderid = ship.orderid
WHERE order.userid = 1

If you want all OrderID's that exist in the Order table (whether they exist in Ship or not), you would use this:

select orderid FROM order WHERE order.userid = 1

[khandu]

works the 1st one.. thanks

Just one more thing

How about if i want only those orderID which DO NOT exist in ship ( just the opposite )

what will be the change?

select order.orderid FROM order INNER JOIN ship ON order.orderid = ship.orderid

will that = change to NOT = ???

[si_the_geek]

Good guess, but no - that does something very different indeed! It does an almost Cartesian join (very bad!).. it would return each value from Order X times, where X is the number of records in ship that aren't the same as it.

Assume that order contains these OrderIDs: 1, 2
..and that Ship contains these: 1, 3
..it would return:
1 (ship.orderid = 3)
2 (ship.orderid = 1)
2 (ship.orderid = 3)

To do what you want, you need to use a sub-query to find all the values from Ship, and allow anything that isn't in that list:

SELECT order.orderid 
FROM order 
WHERE order.orderid NOT IN (SELECT ship.orderid FROM ship)

[khandu]

duh.. it was THAT simple.. dumb me..

thankx a lot.. repped and resolved