February 26, 2015 at 6:28 am
Hello all,
need a big assistance, please could someone help me to convert this queries to sql queries
thanks in advance
SELECT DISTINCT [IDEA Attribute Templates Distinct].Segment, [IDEA Attribute Templates Distinct].Family, [IDEA Attribute Templates Distinct].Class, [IDEA Attribute Templates Distinct].Category, [IDEA NSIIndustriesInc10875117 AM].UNSPSC, [PPO All Suppliers with IDEA Seller ID].[PPO All Suppliers].Supplier, [PPO All Suppliers with IDEA Seller ID].[ITEM PART #], [PPO All Suppliers with IDEA Seller ID].DESCRIPTION
FROM ([PPO All Suppliers with IDEA Seller ID] LEFT JOIN [IDEA NSIIndustriesInc10875117 AM] ON ([PPO All Suppliers with IDEA Seller ID].[ITEM PART #] = [IDEA NSIIndustriesInc10875117 AM].[Catalog Number]) AND ([PPO All Suppliers with IDEA Seller ID].[IDW Seller ID] = [IDEA NSIIndustriesInc10875117 AM].[Seller ID])) LEFT JOIN [IDEA Attribute Templates Distinct] ON [IDEA NSIIndustriesInc10875117 AM].UNSPSC = [IDEA Attribute Templates Distinct].UNSPSC
WHERE ((([PPO All Suppliers with IDEA Seller ID].[Target Suppliers v2].Supplier)="NSI"))
ORDER BY [IDEA Attribute Templates Distinct].Segment, [IDEA Attribute Templates Distinct].Family, [IDEA Attribute Templates Distinct].Class, [IDEA Attribute Templates Distinct].Category, [IDEA NSIIndustriesInc10875117 AM].UNSPSC, [PPO All Suppliers with IDEA Seller ID].[PPO All Suppliers].Supplier, [PPO All Suppliers with IDEA Seller ID].[ITEM PART #];
February 26, 2015 at 7:20 am
Have you got the SQL table definitions?
February 26, 2015 at 7:25 am
Yes i do, its exactly the same table in sql server
February 26, 2015 at 7:27 am
i have 3 tables
Idea_atttribute_schema (template of products)
Am_data (consist of attributes data to products)
purchase order( consist of different suppliers)
February 26, 2015 at 7:41 am
lanreok99 (2/26/2015)
i have 3 tablesIdea_atttribute_schema (template of products)
Am_data (consist of attributes data to products)
purchase order( consist of different suppliers)
That isn't what Laurie meant. She really was asking if you could post the actual definitions, not just some names that don't help us much.
My question is, what have you tried? Aside from being hideous from a formatting and readability perspective (Access creates horrible formatting) this should work unchanged.
I would make a couple of suggestions though. I would change the table names and get rid of the spaces, that just makes queries a LOT more difficult to write and read. The second suggestion would be to use aliases (this is not something Access does).
Here is your query after running it through a formatter.
SELECT DISTINCT [IDEA Attribute Templates Distinct].Segment
, [IDEA Attribute Templates Distinct].Family
, [IDEA Attribute Templates Distinct].Class
, [IDEA Attribute Templates Distinct].Category
, [IDEA NSIIndustriesInc10875117 AM].UNSPSC
, [PPO All Suppliers with IDEA Seller ID].[PPO All Suppliers].Supplier
, [PPO All Suppliers with IDEA Seller ID].[ITEM PART #]
, [PPO All Suppliers with IDEA Seller ID].DESCRIPTION
FROM ([PPO All Suppliers with IDEA Seller ID]
LEFT JOIN [IDEA NSIIndustriesInc10875117 AM]
ON ( [PPO All Suppliers with IDEA Seller ID].[ITEM PART #] = [IDEA NSIIndustriesInc10875117 AM].[Catalog Number] )
AND ( [PPO All Suppliers with IDEA Seller ID].[IDW Seller ID] = [IDEA NSIIndustriesInc10875117 AM].[Seller ID] ))
LEFT JOIN [IDEA Attribute Templates Distinct]
ON [IDEA NSIIndustriesInc10875117 AM].UNSPSC = [IDEA Attribute Templates Distinct].UNSPSC
WHERE (( ( [PPO All Suppliers with IDEA Seller ID].[Target Suppliers v2].Supplier ) = "NSI" ))
ORDER BY [IDEA Attribute Templates Distinct].Segment
, [IDEA Attribute Templates Distinct].Family
, [IDEA Attribute Templates Distinct].Class
, [IDEA Attribute Templates Distinct].Category
, [IDEA NSIIndustriesInc10875117 AM].UNSPSC
, [PPO All Suppliers with IDEA Seller ID].[PPO All Suppliers].Supplier
, [PPO All Suppliers with IDEA Seller ID].[ITEM PART #];
This is better but still pretty awful. All the spaces in your column and table names makes this just incredibly difficult to decipher.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply