|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
September 13th, 2004, 05:06 AM
|
|||
|
|||
|
help with query please
Hello,
I have these tables: Code:
CREATE TABLE Item (
cat_ref VARCHAR(5)PRIMARY KEY,
descrip VARCHAR(50),
cat_type VARCHAR(20),
notes VARCHAR(500))
CREATE TABLE CItem (
contract_nm varchar(10)FOREIGN KEY
REFERENCES Contract(contract_nm),
cat_ref varchar(5) FOREIGN KEY REFERENCES Item(cat_ref)
CONSTRAINT pk_ConItem PRIMARY KEY (contract_nm, cat_ref))
CREATE TABLE Contract (
contract_nm VARCHAR(10) PRIMARY KEY)
What I want to do is select an item with a specific cat_ref that is listed against every contract. My query for selecting a specific contract goes like this: Code:
SQLStmt = "SELECT * " SQLStmt = SQLStmt & "FROM item, citem WHERE "&selection&" = '"&textentered&"'and citem.contract_nm = '"&contract_nm&"' and citem.cat_ref = item.cat_ref" So that selects a specific item that is valid for a specific contract, but I need to find an item valid for ALL contracts? I've tried various joins etc but it ends up incredibly messy and I get the item listed 5 times (for 5 contracts) If anyone can help it would be very much appreciated. Thanks 
|
|
#2
September 13th, 2004, 12:37 PM
|
|||
|
|||
|
Quote:
SELECT DISTINCT on that messy query should help. or (untested), select catref from item where catref not in (select distinct i.catref from item i left join citem ci on i.catref=ci.catref where ci.catref is null) The left join includes all records from item, and where there is no match on the other side you get nulls. So any item with a null cannot be one that matches every time. All the others do match every time. tim
|
|
#3
September 14th, 2004, 08:14 AM
|
|||
|
|||
|
Quote:
This returns all items that are valid on any contract, not items valid for all contracts. 
|
|
#4
September 15th, 2004, 03:54 PM
|
|||
|
|||
|
Quote:
Sorry, got them switched in my brain. Just put the right tables/fields in, if you get the idea of the query then you shouldn't have a problem.
|
|
| Viewing: Dev Shed Forums > Databases > MS SQL Development > help with query please |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
