I'm Learning SQL... easy question(I think)

[demon42]

(see my last post for clarification)

I'm learning SQL, and this is probably very easy, BUT-


So lets say I have a table('supply') in my database of suppliers('sp') and part numbers ('pnum').
How would I do something like: "Display all supplier/part pairs for which the supplier has never shipped the part."

I have some more questions... I will try to figure them out once i get this answer... but I may post again!


THANK YOU! (in advance)

 

[theknight571]

Do you have Access? If so, then you might want to build a dummy DB in Access and use it to build your queries. Access has an option where you can see your query in SQL.

It might not be 100% SQL Standard accurate (it's been a long time since I had to use straight SQL) but for learning, examples and testing it'd get you close.

If you can list your tables/fields then I might be able to get you what you're looking for... unless you'd just like me to make up my own table and field names.

 

[demon42]

I actually don't have Access... but I'm guessing, knowing MS, the "SQL Equivalent" output would be overly convoluted and confusing- hardly conducive to learning SQL. Am I wrong?

 

[spyordie007]

Assuming you have a field that denotes records that have shipped in a table that records transactions:
SELECT transactionnumber
FROM transactiontable
WHERE shipped = '0'

However no offence but if you havent figured this out you really need to be taking some time to do some proper design before writing your SQL queries.

 

[Drakkon]

yes you are wrong...i always use access in conjunction with oracle to decipher some of the simpler queries that have a buncha where clauses or table joins beause its easier to visualize them in access and the SQL you get out is frankly pretty clean.

as for you query it would go soemthing like (assuming you have a transactions table or the like)
select pnum,sp from supple where pnum not in (select pnum from transactions);

 

[demon42]

Hi, thank you for trying, but either I don't understand, or I wasn't clear in my orignial question, so I will try again.
Given the following two tables:

TableNameart
p_num
-------------
p1
p2
p3
p4
p5
p6

TableName:shipment
sup_num|p_num|qty
-----------------------------------------------
s1|p1|100
s1|p2|400
s1|p4|200
s1|p6|200
s2|p2|300
s2|p3|300
s3|p1|100
s3|p5|500
s4|p4|200
s4|p5|100
s4|p6|200


I would like a query that returns the supplier numbers with part numbers that have NOT been shipped before:
sup_nump_num (expected output from SQL query)
----------------------
s1|p3
s1|p5
s2|p1
s2|p4
s2|p5
s2|p6
s3|p2
s3|p3
s3|p4
s3|p6
s4|p1
s4|p2
s4|p3



I'm not certain what my hang-up is, but it seems that I have to find part numbers that are distinct for each supplier and then somehow match and remove those parts from the parts list for each supplier? please help!

 

[demon42]

bumping from page 2... I still haven't got it...

 

[Markbnj]

You have to relate the two tables, as Drakkon said... but you have an issue with the example tables you posted. Because sup_num only shows up in shipment, and you are specifically excluding rows that appear in shipment, you won't get any sup_nums back.

I assume this is an oversight, because a reasonable design would have sup_num in the part table. Your tables could look like this:

table: part

sup_num | p_num
________________
s1 p1
s1 p2
s2 p3
s2 p4
s3 p5

table: shipment

sup_num | p_num | qty
______________________
s1 p2 200
s2 p3 100
s3 p5 150

With this schema you can do:

SELECT SUP_NUM, P_NUM FROM PART WHERE P_NUM NOT IN
(SELECT P_NUM FROM SHIPMENT)

Simple as that. As an earlier posted pointed out... understand the data schema and the table relationships, and get those right before you think about queries.

 

[Seeruk]

Originally posted by: Markbnj
You have to relate the two tables, as Drakkon said... but you have an issue with the example tables you posted. Because sup_num only shows up in shipment, and you are specifically excluding rows that appear in shipment, you won't get any sup_nums back.

I assume this is an oversight, because a reasonable design would have sup_num in the part table. Your tables could look like this:

table: part

sup_num | p_num
________________
s1 p1
s1 p2
s2 p3
s2 p4
s3 p5

table: shipment

sup_num | p_num | qty
______________________
s1 p2 200
s2 p3 100
s3 p5 150

With this schema you can do:

SELECT SUP_NUM, P_NUM FROM PART WHERE P_NUM NOT IN
(SELECT P_NUM FROM SHIPMENT)

Simple as that. As an earlier posted pointed out... understand the data schema and the table relationships, and get those right before you think about queries.

QFT - Really if you get the design right at the start, everything is is very very easy. Make sure you get equated with normalization as early as possible as it will help your design thought processes immensely

 

[Jeraden]

There should really be a separate table of suppliers, I'm assuming he just left that table out as he references it in his original post.
So tables:
suppliers
parts
shipments

Its still a shady design though, as you are then implying that every supplier can ship every single part number.

So to get the answer you want...
First, to get all possible combinations of suppliers/parts:
select *
from parts, suppliers

That's it, there is no join condition, its a full cartesian product since apparently all suppliers can ship all parts. So then take that and exclude the parts that have shipped..

select sup_num, p_num
from parts, suppliers
where not exists
(select 0
from shipments
where shipments.sup_num = suppliers.sup_num
and shipments.p_num = parts.p_num)

That will exclude any supplier/part combos that appear on the shipments table