May 8, 2017 at 12:30 pm
I've two queries.
The first, returns a single column with multiple results.
SELECT
Orders.ID
FROM Company
INNER JOIN User ON Company.usid = User.usid
INNER JOIN Orders ON User.orid = Orders.orid
WHERE
Company.name LIKE '%Excavation%'
AND (Orders.refdate >= CONVERT(DATETIME, '2015-03-25 00:00:00', 102))
This would return
ID
12345
3456412
346536
9787845
449347
4787
79665
The second returns the Company name, the order ID, address and age and other User information.
SELECT
DISTINCT
Company.name AS 'Company'
, Orders.orid AS 'ID'
, Company.address
, ((CONVERT(CHAR(8), GETDATE(), 112)*1 - CONVERT(CHAR(8), User.dob, 112))/10000) AS 'Age'
FROM
FROM Company
INNER JOIN User ON Company.usid = User.usid
INNER JOIN Orders ON User.orid = Orders.orid
WHERE
(Company.name LIKE 'Excavation%')
AND (Orders.refdate BETWEEN '03/25/2015' AND '05/05/2017')
This would return
Company ID address Age
Ted's 12345 123 W. Main 41
Backer 3456412 S. 92nd. 62
Rentals 346536 PO Box 43 52
What I would like to do is tell the second query to just use the first querys results as part of WHERE.
Something like WHERE Orders.orid = (@IDs)
How might something like this be accomplished?
Thanks so much!!
May 8, 2017 at 12:43 pm
Instead of this:WHERE Orders.orid = (@IDs)
You wantWHERE Orders.OrID IN ( SELECT OrID....<rest of select/where clause>)....<rest of select/where clause>)
I think this is right... careful of the parentheses:SELECT DISTINCT Company.name AS 'Company'
, Orders.orid AS 'ID'
, Company.address
, ((CONVERT(CHAR(8), GETDATE(), 112)*1 - CONVERT(CHAR(8), User.dob, 112))/10000) AS 'Age'
FROM Company
INNER JOIN User ON Company.usid = User.usid
INNER JOIN Orders ON User.orid = Orders.orid
WHERE
Company.name LIKE 'Excavation%'
AND Orders.refdate BETWEEN '03/25/2015' AND '05/05/2017'
AND Orders.OrID IN (
SELECT Orders.ID
FROM Company
INNER JOIN User ON Company.usid = User.usid
INNER JOIN Orders ON User.orid = Orders.orid
WHERE Company.name LIKE '%Excavation%'
AND (Orders.refdate >= CONVERT(DATETIME, '2015-03-25 00:00:00', 102))
);
May 9, 2017 at 10:24 am
See, all I thought of was using a declare and setting it to a query, but with multiple results it never would have worked.
A subquery! Thanks for showing me the forest when I was too busy looking at the trees!!
May 10, 2017 at 4:29 pm
I would also suggest you Google CTEs (Common Table Expressions), table variables, and the EXISTS / NOT EXISTS operator. Those may be very helpful concepts as you do more tasks like this in the future.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply