I haven’t been following closely enough (and it’s hard for me to do this without actually seeing the tables). Why do you need the subquery on your second statement “(SELECT DISTINCT model, price FROM PC)”?
It just so happens that they are…in this case.
Short answer: Because I don’t know what I’m doing.
Long answer: I don’t know.
ok. I would maybe try and remove that. Maybe make the second statement the same as the first, but just replace Laptop with PC?
On a side note, my last job was very SQL heavy. Just got a new job and it is very SQL light. Probably normal to happen as you get more experience and move up the ladder, but I do find myself missing SQL (and the really nice EDW from my last company).
Okay, (1) I finally found the recommended solution online (2) all I needed to do was add the third table in (even though it didn’t have any matching models) for completeness.
Here’s the final code that produced an acceptable result:
SELECT DISTINCT PRODUCT.model, LAPTOP.price AS PRICE
FROM Product
INNER JOIN Laptop
ON Product.model = Laptop.model
WHERE PRODUCT.maker = 'B'
UNION
SELECT DISTINCT PRODUCT.model, PC.price AS PRICE
FROM Product
INNER JOIN (SELECT DISTINCT model, price FROM PC) AS PC
ON Product.model = PC.model
WHERE PRODUCT.maker = 'B'
UNION
SELECT DISTINCT PRODUCT.model, PRINTER.price AS PRICE
FROM Product
INNER JOIN (SELECT DISTINCT model, price FROM PRINTER) AS PRINTER
ON Product.model = PRINTER.model
WHERE PRODUCT.maker = 'B'Thanks all for helping pull me through this.
Yeah…I miss SAS…and this is one rickety ladder that I’m on right now, too.
I went from Microsoft SQL to SAS. I thought it’d be fine, but I’m not required to use it enough to make things stick, so I find I just have to ask other people to pull data for me.
What packages do you (or others) use in SAS? We only have base, so I’m limited, though for data pulling it’s generally plenty.
In my life insurance job, we only used base…I’m pretty sure.
The main things I needed to know how to do were “INFILE INPUT” “IF THEN ELSE”/“SELECT OTHERWISE” “MERGE” “PROC SUMMARY” “FILE PUT”
I also used macros quite a bit just to eliminate duplicate code.
When I got my ass canned from that job I did a phone interview with a health company that used SAS. The actuary wasn’t too impressed with my skillz and didn’t give me much information on what she was expecting, but I think she really wanted me to drop some big SASsy words like SAS/STAT, ANOVA, and logistic regression.
That’s how it was for me for SQL…until now…now it needs to stick…hence, this thread. 
Much the same. Need to learn more macros I think. Only really use them now for looping through repetitive data steps.
Have you tinkered with Proc SQL?
1 Like
Anytime I do anything with SAS it is with Proc SQL statements. Anything more complicated I just let the SAS person on our team do it (and usually I’m just tweaking already created SQL statements).
1 Like
Third table! Goalpost mover! 
The “less than by 8” response had me shaking my head thinking they actually wanted duplicate records.
You were asking about how to join the Laptop table in as well. The key concept is you aren’t trying to match laptop records to pc records to printer records, you are trying to match product records to pc records, and product records to laptop records, and product records to printer records, because the product record has the maker information on it and those other records have the price information (all tables have product field). So you can either join three times and union the matches, or union the selection from the three tables (product, price) and join the resulting records to the product table.
If you do use a join across all three tables (you would code successive join statements to do this), the issue you have to deal with is the PRICE field comes in from each of the tables. Which price do you use? COALESCE can help if there are NULLs (which would happen if each product isn’t in every one of the three tables), but COALESCE can’t give multiple prices if the product is on more than one of the three (I think it chooses the first one that is nonnull).
2 Likes
Question: could the same model have a PC and Laptop version?
Another question: is it obvious from the model if it’s a PC or a Laptop?
And, just an FYI. UNION removes dups. UNION ALL does not. For the set theorists in the house, UNION creates a set, UNION ALL creates a multi-set.
I forgot this. Good point!
Interesting, would love to see the errors but it’s no matter. Maybe I made a syntax error.
You maybe could have taken all that (without isnull()) and wrapped it as another subquery, doing
SELECT Model, isnull(Price1,Price2) AS Price
FROM (everything else)
That might be terribly optimized, I’m not great at optimization except a few obvious things like setting keys but not too many keys, etc.
I just always use UNION ALL. If I have to use a UNION query at all.
sql-ex-ru <3 </3 <3
shiii… I used this website over a decade ago, when I was first applying for Entry Level jobs and the screener mentioned SQL. I just logged back in… Looks like I skipped this problem. Hahahaha.
It really is a wonderful site. I learned more in that 1 week of pre-interview cramming than my entire actuarial career.
1 Like
Yeah, I’m a little concerned about what sorts of mischief Putin & his Kronies are up to. Otherwise, so far, so good.