quote:

---

Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)

---

quote:

---

Originally posted by TG

>>Thank you very much for your time.
You're very welcome.

Well, the best way to do this is the way you did initially using an inner join. Although in the industry these days it would be preferred to use the newer JOIN syntax rather than the comma seperated list of tables. So I think the request to use a subquery just for "subquerying" sake is not a good idea.

But just to satisfy the requirement, you know you need to select out of the orders table because that is where all the detail is. The only other thing you need is the customer name (customer table). So I see three possibilites: you could either simply reverse your logic (use where exists from the customer table) or make your subquery a derived table - which is a subquery in the FROM clause instead of the where clause. Or third option is to use a subquery right in the SELECT statement to define the CustomerName value.

Be One with the Optimizer
TG

---

quote:

---

is really getting frustrating.

---

quote:

---

Originally posted by TG

quote:

---

is really getting frustrating.

---

We never decided on a "safe word" for you to use that will make me stop torturing you If you want an actual answer, just work "Mango" into a sentence.

Your attempt to put the subquery into the SELECT clause was close, you just forgot to make a complete select statement without the subquery:
ie
SELECT column1, column2
FROM someTable
Where some criteria

Then once you have a working statement you can add a new column3 to the SELECT like:
,Column3 = (select customerName from customer where customerID = orders.customerid)

Be One with the Optimizer
TG

---

quote:

---

Originally posted by Caldrumr

Okay, I'm afraid this whole section is just driving me crazy. I am close to giving up here. And this is just one question out of ten; I won't have any hair left after I pull it out.

I tried to change it how you said and got:

SELECT CustomerID, CAST(OrderDate AS CHAR (11) )AS OrderDate FROM Orders a
WHERE a.OrderDate BETWEEN '11/1/1996' AND '11/30/1996'
AND EXISTS (SELECT CompanyName FROM Customers b WHERE a.CustomerID = b.CustomerID ) ;

However, this still does not list the Company Name. I am not sure what to do. And looking at later questions, I think I am in a whole lot of trouble this time....

---

quote:

---

You are almost there. Just remember that whatever detail you want need to be in select list and also make sure you add condition which checks whether details are actually present in related table. Keeping this in mind, look into bol for clauses which you think you can use to achieve it.

---

--as we determined earlier this is the correct way get that info (no subquery)
SELECT	a.CustomerID
	,CAST(a.OrderDate AS CHAR (11) ) AS OrderDate 
	,companyName
FROM	Orders a
JOIN	Customers b 
		ON a.CustomerID = b.CustomerID 
where	a.OrderDate BETWEEN '11/1/1996' AND '11/30/1996'


--However, just to satisfy the stupid requirement of how could you do it with a sub-query
--This is what I was hinting towards in my post from (01/14/2008 :  19:54:55)
SELECT	a.CustomerID
	,CAST(a.OrderDate AS CHAR (11) ) AS OrderDate 
	,companyName = (select companyname from customers where customerid = a.customerid)
FROM	Orders a
where	a.OrderDate BETWEEN '11/1/1996' AND '11/30/1996'

quote:

---

We never decided on a "safe word" for you to use that will make me stop torturing you If you want an actual answer, just work "Mango" into a sentence.

Your attempt to put the subquery into the SELECT clause was close, you just forgot to make a complete select statement without the subquery:
ie
SELECT column1, column2
FROM someTable
Where some criteria

Then once you have a working statement you can add a new column3 to the SELECT like:
,Column3 = (select customerName from customer where customerID = orders.customerid)

---

quote:

---

Originally posted by TG

MANGO! MANGO!

Ok that is a little overkill. But all you need is to add ",b.companyname" to your select list.

--as we determined earlier this is the correct way get that info (no subquery)
SELECT	a.CustomerID
	,CAST(a.OrderDate AS CHAR (11) ) AS OrderDate 
	,companyName
FROM	Orders a
JOIN	Customers b 
		ON a.CustomerID = b.CustomerID 
where	a.OrderDate BETWEEN '11/1/1996' AND '11/30/1996'


--However, just to satisfy the stupid requirement of how could you do it with a sub-query
--This is what I was hinting towards in my post from (01/14/2008 :  19:54:55)
SELECT	a.CustomerID
	,CAST(a.OrderDate AS CHAR (11) ) AS OrderDate 
	,companyName = (select companyname from customers where customerid = a.customerid)
FROM	Orders a
where	a.OrderDate BETWEEN '11/1/1996' AND '11/30/1996'

EDIT:
I guess the date - time will post different depending on where you are so forget the "in my post from (01/14/2008 : 19:54:55)"

quote:

---

We never decided on a "safe word" for you to use that will make me stop torturing you If you want an actual answer, just work "Mango" into a sentence.

Your attempt to put the subquery into the SELECT clause was close, you just forgot to make a complete select statement without the subquery:
ie
SELECT column1, column2
FROM someTable
Where some criteria

Then once you have a working statement you can add a new column3 to the SELECT like:
,Column3 = (select customerName from customer where customerID = orders.customerid)

---

Be One with the Optimizer
TG

---

SELECT a.EmployeeID, a.LastName, a.FirstName, 
(SELECT LastName FROM Employees WHERE EmployeeID=a.ReportsTo)
AS SupervisorLast, 
(SELECT FirstName FROM Employees WHERE EmployeeID=a.ReportsTo)
AS SupervisorFirst
FROM Employees a
ORDER BY a.EmployeeID ;

quote:

---

Originally posted by visakh16

You can do something like this:-

SELECT a.EmployeeID, a.LastName, a.FirstName, 
(SELECT LastName FROM Employees WHERE EmployeeID=a.ReportsTo)
AS SupervisorLast, 
(SELECT FirstName FROM Employees WHERE EmployeeID=a.ReportsTo)
AS SupervisorFirst
FROM Employees a
ORDER BY a.EmployeeID ;

---