Avatar of johnnyg123
johnnyg123Flag for United States of America

asked on 

sql to add additional rows

Here is some sample data from the order and customer tables (SQL Server 2014)

Order Table
OrderID  OrderAmount    
1              300    
2              400          


Customer Table
CustomerID  CustomerFirstName CustomerLastName
500                Fred                          Smith

Need to write a query that will return all rows from customer table with a row for each order that exists

Given sample data would like query to return the following

CustomerID  CustomerFirstName CustomerLastName OrderID  OrderAmount
500                Fred                          Smith                        1             300
500                Fred                          Smith                        2             400

Not sure of best way to do this


SQLMicrosoft SQL Server

Avatar of undefined
Last Comment
Jim Horn
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try:

select c.*, o.*
from Customer c, Order o

Open in new window

But you really want to know what you want to return, and if this is the expected result
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

>>from Customer c, Order o

That will work but probably better to use the new ANSI join syntax.
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Hi,
Try
select a.*,b.*
from customertable,ordertable

Open in new window

I think better to have/add customerID column (as one foreign key) in Ordertable, to know which customer does make which order.


@Peter,

Please review previous Experts comments.  That has already been posted.

and yours is incorrect.  You cannot select a.* if you don't alias the tables.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

:: snicker ::   This question was answered in the first comment, with helpful commentary.
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo