Join and rename headers? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join and rename headers?

Author

Topic

deanfp
Starting Member

26 Posts

Posted - 2013-04-15 : 11:14:09

Hi

I am trying to obtain some information to download in a csv file from two SQL tables so that they come out in one row such as this as an example

first_name,email,purchase_date,delivery_date,customer_ref,order_ref,sku,postcode,currency,price
Ravi,customer1@example.com,12/01/2013,15/01/2013,122255,12354,THB101,SE1 8NZ,GBP,20
Jay,customer2@example.com,06/02/2013,10/02/2013,12157,34526,THB102,NW1 8NZ,17.99

So far I have this SQL code that extracts all but the sku,currency and price

SELECT FirstName,Email,OrderDate,ShippedOn,CustomerID,OrderNumber,ShippingZipfrom Orders

The sku, and price are in a table called Orders_ShoppingCart and the fields I need are

OrderedProductSKU and OrderedProductPrice.

Both SQL tables have a common field in OrderNumber

But however I try to JOIN these it does not work. Any ideas? And also is is possible to rename SQL headers when outputing a csv file?

Thanks!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-04-15 : 11:29:46

This might Work for you:


SELECT Orders.FirstName
      ,Orders.Email
      ,Orders.OrderDate
      ,Orders.ShippedOn
      ,Orders.CustomerID
      ,Orders.OrderNumber
      ,Orders.ShippingZip as Zip
      ,Orders_ShoppingCart.OrderedProductSKU as SKU
      ,Orders_ShoppingCart.OrderedProductPrice as Price
 from Orders
      left outer join Orders_ShoppingCart
                   on Orders_ShoppingCart.OrderNumber=Orders.OrderNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-15 : 12:21:26

for renaming column use aliases like below


SELECT Orders.FirstName AS Name
      ,Orders.Email AS EmailAddress
      ,Orders.OrderDate 
      ,Orders.ShippedOn
      ,Orders.CustomerID
      ,Orders.OrderNumber
      ,Orders.ShippingZip as Zip
      ,Orders_ShoppingCart.OrderedProductSKU as SKU
      ,Orders_ShoppingCart.OrderedProductPrice as Price
 from Orders
      left outer join Orders_ShoppingCart
                   on Orders_ShoppingCart.OrderNumber=Orders.OrderNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

deanfp
Starting Member

26 Posts

Posted - 2013-04-15 : 14:53:23

Thanks! Worked great!

deanfp
Starting Member

26 Posts

Posted - 2013-04-15 : 15:08:57

If I wanted to insert a dummy row that does not exist in the SQL table is that possible? It would be curreny with the same value for all USD

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-16 : 02:14:59

quote:
Originally posted by deanfp

If I wanted to insert a dummy row that does not exist in the SQL table is that possible? It would be curreny with the same value for all USD

its possible

use like


INSERT Table (col1,Col2,...)
VALUES (value1, value2 ,...)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources