Access SQL

[Sockpuppet]

Right trying to do this and it is annoying me.

Got 2 queries.

SELECT DISTINCT Hierarchy.Mapping
FROM Hierarchy
WHERE (((Hierarchy.Department_Number)=170) AND ((Hierarchy.Section_Number)=510));

and

SELECT *
FROM [IS]
WHERE ((([IS].Country)='CZ') AND (([IS].HMapping)="H11AA"));

The first returns several records and I need to select from the second based on these like having HMapping = x Or Y OR z Or etc

I've tried a join from HMapping to Mapping (table relationships) but it returns one record from the IS for each mapping i.e. each line about 1000 times.

I think I need a nested SQL statement but can for the life of me think where to start on this one.

If that fails I'll have to store the items in an array and formulate a sql statement but a nested SQL will be far easier?

Ideas?

[DaveB]

Right trying to do this and it is annoying me.

Got 2 queries.

SELECT DISTINCT Hierarchy.Mapping
FROM Hierarchy
WHERE (((Hierarchy.Department_Number)=170) AND ((Hierarchy.Section_Number)=510));

and

SELECT *
FROM [IS]
WHERE ((([IS].Country)='CZ') AND (([IS].HMapping)="H11AA"));

The first returns several records and I need to select from the second based on these like having HMapping = x Or Y OR z Or etc

I've tried a join from HMapping to Mapping (table relationships) but it returns one record from the IS for each mapping i.e. each line about 1000 times.

I think I need a nested SQL statement but can for the life of me think where to start on this one.

If that fails I'll have to store the items in an array and formulate a sql statement but a nested SQL will be far easier?

Ideas?

SQL isnt my strong point but would a CASE statement work for this?

CASE
  WHEN booleanExpression1 THEN result1
  WHEN booleanExpression2 THEN result2
  ...
  WHEN booleanExpressionN THEN resultN
  
  [
    ELSE elseResult
  ]
END

Or somthing similar?

[Sockpuppet]

Thanks for the reply...was suprisingly easy in the end.

SELECT i.CE_Identifier, i.EQOS_Ref, i.UK_Prod_Description, i.IS_Line_Status,
 i.UK_CE_Only, i.UK_Prod_Type, i.UK_Season, i.Line_Status, i.UK_TPN,
i.Product_Barcode_EAN, i.ITF_14, i.Case_Size, i.Brand, i.UK_Performer,
i.VPN, i.Supplier_Name, i.Supplier_Number, i.IS_Factory, i.Port, i.COO,
i.Depot_Stream, i.Delivery_Method, i.Case_Width, i.Case_Length, 
i.Case_Height, i.COP, i.Unit_1CP, i.Retek_Cost, i.FCL_Insurance, 
i.FCL_Freight, i.FCL_Basic, i.IS_Admin_Cost, i.Duty, i.FCL_ELC, 
i.Landed_Unit_Cost, i.UK_ROS FROM [IS] As i WHERE 
(((i.Country)=""" & CountryCode & """) AND ((i.COP) <> """") AND 
HMapping IN (SELECT DISTINCT Hierarchy.Mapping As HMapping 
FROM Hierarchy WHERE (((Hierarchy.Department_Number)=" & DepNum & ") 
AND ((Hierarchy.Section_Number)=" & SectionNum & "))));

[lilelvis2000]

Thanks for the reply...was suprisingly easy in the end.

SELECT i.CE_Identifier, i.EQOS_Ref, i.UK_Prod_Description, i.IS_Line_Status,
 i.UK_CE_Only, i.UK_Prod_Type, i.UK_Season, i.Line_Status, i.UK_TPN,
i.Product_Barcode_EAN, i.ITF_14, i.Case_Size, i.Brand, i.UK_Performer,
i.VPN, i.Supplier_Name, i.Supplier_Number, i.IS_Factory, i.Port, i.COO,
i.Depot_Stream, i.Delivery_Method, i.Case_Width, i.Case_Length, 
i.Case_Height, i.COP, i.Unit_1CP, i.Retek_Cost, i.FCL_Insurance, 
i.FCL_Freight, i.FCL_Basic, i.IS_Admin_Cost, i.Duty, i.FCL_ELC, 
i.Landed_Unit_Cost, i.UK_ROS FROM [IS] As i WHERE 
(((i.Country)=""" & CountryCode & """) AND ((i.COP) <> """") AND 
HMapping IN (SELECT DISTINCT Hierarchy.Mapping As HMapping 
FROM Hierarchy WHERE (((Hierarchy.Department_Number)=" & DepNum & ") 
AND ((Hierarchy.Section_Number)=" & SectionNum & "))));

You could also have created your Select Distinct as a query and then performed a join from HMapping to it. similar to

select .....blah.... from [IS] inner join (select distinct HMapping ...blah...) Map on [IS].HMapping=Map.HMapping;

Cheers.

[Sockpuppet]

Cool. Thanks for the help guys

[Weltchy]

For an even more obscure way of doing this, giving you the much needed little to no performance gain whatsoever (Could even be slower), you could have used a select into a temporary table and then joined on the required temporary table.

Remember, extra code for no performance gain = The contractors holy grail, making you look like you've had to do lots of work when in fact you've done little.

[Sockpuppet]

Anyone know a quick an dirty way to reverse this.

select * from [IS] inner join (SELECT DISTINCT Mapping.Mapping As HMapping FROM Mapping) Map on [IS].HMapping=Map.HMapping;

SELECT * [IS] WHERE HMapping IN  (SELECT DISTINCT Mapping.Mapping As HMapping FROM Mapping);

Instead of it selecting records are are found in the Mapping table I am looking to make it select records that are not equal to the mapping in the mapping table.

Is there such a function as NotIn for Access?

Sockpuppet in "i'm not an IT guy" mode.

[TheFaQQer]

Anyone know a quick an dirty way to reverse this.

select * from [IS] inner join (SELECT DISTINCT Mapping.Mapping As HMapping FROM Mapping) Map on [IS].HMapping=Map.HMapping;

SELECT * [IS] WHERE HMapping IN  (SELECT DISTINCT Mapping.Mapping As HMapping FROM Mapping);

Instead of it selecting records are are found in the Mapping table I am looking to make it select records that are not equal to the mapping in the mapping table.

Is there such a function as NotIn for Access?

Sockpuppet in "i'm not an IT guy" mode.

Try "NOT IN" - think it's there in Access, since it's a SQL thing.

[Sockpuppet]

Doh.

I used NotIn instead of Not In

Cheers fella, need to get back to warehouses or trucking. All this access and SQL is starting to make me a sad boy.