SQL - All shipping Key <> ProductsID

I'm now trying to figure out what products don't have shipping information.
I have a products table and a shipping table. The shipping table has a key
to the products id so I can select all the shipping that has a product
association but what I really need to know is which products I don't have a
shipping number

I get what I think are strange results - I just don't understand why

SELECT dbo.Products.ID AS ProductID, dbo.atblShipping.*
FROM dbo.atblShipping INNER JOIN
dbo.Products ON dbo.atblShipping.ShipKey =
dbo.Products.ID

V
2735B - 1419 - 2735B - CO - 14.75 - 9.75 - 2 - 1.5
41500 1351 41500 WI 10 24 0.5 2.5
44320 1369 44320 WI 27 26 17.5 54
3802-S 1459 3802-S CO 7 5 3 0.5



SELECT dbo.Products.ID AS ProductID, dbo.atblShipping.*
FROM dbo.atblShipping INNER JOIN
dbo.Products ON dbo.atblShipping.ShipKey <>
dbo.Products.ID

Notice all the rows have 5726G

2735B 1 5726G CO 6 6 3 1.1
T-617 1 5726G CO 6 6 3 1.1
T-58 1 5726G CO 6 6 3 1.1
41500 1 5726G CO 6 6 3 1.1

"lee" <lfairban_nospam (AT) amep (DOT) com> wrote

I get what I think are strange results - I just don't understand why

You just don't understand joins. When you join two tables, you can imagine
that they are multiplied together (this doesn't happen in practice, but it's
a good visualization) and that every row in the first table is matched with
every row in the second table. The join condition (ON ...) tells the DB
which rows to keep. So if you join where two fields are not equal, then you
will get every row in the first table matched to every row in the second
table EXCEPT the row where the field in table one and table two match.

www.sqlcourse.com
www.sqlcourse2.com

Secondly, quit referencing your columns with anything more than the table
name. This isn't just non-standard, but Microsoft has indicated that four
part references (database.owner.table.column) will not work in the next
release of SQL Server (the one after 2005) and only two part references
(table.column) will be supported, so break the habit now.

You want an outer join, and you want to keep only the rows where the right
hand table had no match - that is, where there is no shipping information
for the product.

SELECT Products.ID AS ProductID, atblShipping.*
FROM dbo.Products
LEFT OUTER JOIN dbo.atblShipping ON Products.ID =
atblShipping.ShipKey
WHERE atblShipping.ShipKey IS NULL

Secondly, quit referencing your columns with anything more than the table
name. This isn't just non-standard, but Microsoft has indicated that four
part references (database.owner.table.column) will not work in the next
release of SQL Server (the one after 2005) and only two part references
(table.column) will be supported, so break the habit now.

One of the hosts I work with requires the owner.table.column syntax. Is
there a way around that?

-Darrel

"darrel" <notreal (AT) nowhere (DOT) com> wrote

One of the hosts I work with requires the owner.table.column syntax. Is
there a way around that?

-Darrel

I don't see how they can require it on your columns. On tables, sure - they
may have a single database divided up with each user being just a separate
owner (kind of like a schema works in Oracle), but the column depends on the
table, so...
It's just redundant. For your tables, you should absolutely specify
owner.table, even if everything has an owner of "dbo."
Just out of curiosity - how is such a requirement enforced?

The column syntax change is nothing to worry about in the sort term, though.
2005 was just released, and the syntax was officially deprecated in 2005, so
everyone has until the next release (a couple years maybe?) to change their
code and policies. And nobody will be forced to upgrade, obviously.

It's just redundant. For your tables, you should absolutely specify
owner.table, even if everything has an owner of "dbo."
Just out of curiosity - how is such a requirement enforced?

My queries don't work without it. ;o)

BUT...I think you pointed out my error. As you state, I really only need to
put it on the TABLE itself, not each column.

-Darrel

Thanks...that worked , I think...gave me more results than I really wanted
to see.

Do you know of a way of creating an autonumber of the results that would
indicate how many don't have shipping information? Currently, I have them
autonumbered but there is no way to count so many products. currently, the
list goes like this

2
4
7
8
10...so it doesn't give me an accurate account of how many actually are
listed.

As a note, My version Enterprise managager automatically adds the "dbo." to
all the tablenames in the sql viewer but I will make a note of that lion..

"lee" <lfairban_nospam (AT) amep (DOT) com> wrote

Thanks...that worked , I think...gave me more results than I really wanted
to see.

2
4
7
8
10...so it doesn't give me an accurate account of how many actually are
listed.

Any version prior to SQL Server 2005 cannot number the rows in a query
(well, it can, but it's more trouble that it's worth and doesn't always work
the way you want it to). My advice would be to run the query in Query
Analyzer with the output set to "grid" (grid is the default with 2000;
earlier versions default to text).

As a note, My version Enterprise managager automatically adds the "dbo."
to all the tablenames in the sql viewer but I will make a note of that
lion..

TABLE names, yes. COLUMN names, no. No version of EM has ever done that.