return value where join table does include another value

[kpal29]

I have 3 tables. A container table. Each container has one or more cargolines stored in a cargoline table. Each cargoline has one or more invoice lines with one or more charge codes stored in an invoiceline table.

container table
containerID, Arrival Date, Vessel, measurement
111 1-5-06 Santa Maria 100.00

cargoline table
cargolineid, containerid, commodity, measurement
222 111 clothes 50.00
333 111 clothes 50.00

invoiceline table
invoicelineid, cargolineid, chargecode amount
333 222 BAM 10.00
334 222 DAF 15.00
335 333 BAM 10.00
336 333 DAF 15.00


The sample container has cargolines that have invoicelines with charge codes = BAM and DAF. I would like to return all containers that do not have a BAM charge on any of it's cargolines. I have tried using a having clause and regular where clauses but it only seems to give me results where if there is one invoiceline that is <> BAM, it will return the container even if another invoiceline does have BAM. I need it to evaluate all invoicelines on all cargolines before deciding to return the container.

Hope I gave enough information! Any help is appreciated.

Thanks

 

[vongrunt]

Return containers without any BAM charge...

-- one way
select C.*
from container C
left outer join
(	select CL.containerID
	from cargoline CL 
	inner join invoiceLine IL on CL.cargolineID = IL.cargolineID 
		where IL.chargecode='BAM'
) X
on C.containerID = X.containerID
where X.containerID is null

-- another way
select C.*
from container C
where C.containerID not in
(	select CL.containerID
	from cargoline CL 
	inner join invoiceLine IL on CL.cargolineID = IL.cargolineID 
	where IL.chargecode = 'BAM'
)

-- and another
select C.*
from container C
where 'BAM' <> ALL
(	select IL.chargecode
	from cargoline CL 
	inner join invoiceLine IL on CL.cargolineID = IL.cargolineID 
	where CL.containerID = C.containerID
)

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

ALL and ANY is not used that much at all is it?
Of course NOT EXISTS is a 4th way

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

> ALL and ANY is not used that much at all is it?

True... too bad because ALL/ANY|SOME allow some interesting constructs with operators like <, <=, >=, >...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[kpal29]

Thanks for quick reply. I chose to try:

select
dbo.tcontainer.snumber
from
dbo.tcontainer
where
dbo.tcontainer.liD not in
( select dbo.tcontainer.liD
from dbo.d_cargo_line
inner join dbo.invoice_line on dbo.d_cargo_line.cargo_line_ID = dbo.d_invoice_line.cargo_line_ID
where dbo.d_invoice_line.charge_code_id is not 'BAM' )

However, I keep getting this syntax error:
"line 1 incorrect syntax near '.'

What am I still doing wrong?

 

[vongrunt]

Replace IS NOT with =

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mohanmsc]

please any one help

i want to join two tables that are in two different sql server.