question

sqlqa avatar image
sqlqa asked

Write a Query

Hi, Find the Attachment ![alt text][1] This Question was asked by interview. Any one Can answer this question Write Query B shaded Area? [1]: /storage/temp/1559-b+shaded+area.jpg
tsqlinterview-questions
b shaded area.jpg (34.6 KiB)
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
What was your answer?
1 Like 1 ·
Mister Magoo avatar image Mister Magoo commented ·
SELECT * FROM [Table B] WHERE Shaded=1; #geek
1 Like 1 ·

4 Answers

·
sqlqa avatar image
sqlqa answered
SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null THIS is the answer
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
Logically what area is defined by the shading on TableB? Just defining the logical will lead you to several possible solutions to writing a query. It's everything from TableB that doesn't match anything in TableA. From there, could you write a query?
3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Sure it's possible. Looks like you have it figured out. It's all about figuring out the logic needed to eliminate matching values in TableB and only return non-matching. NOT EXISTS will work. As will an OUTER JOIN.
1 Like 1 ·
sqlqa avatar image sqlqa commented ·
Hi Grant Fritchey Here Table A join Table B but we have to write a query to get B records but not matching Table A record. Is it possible to write query?
0 Likes 0 ·
sqlqa avatar image sqlqa commented ·
Can we use NOT EXISTS or Except Operator or RIGHT OUTER JOIN in our Case?
0 Likes 0 ·
Fatherjack avatar image
Fatherjack answered
Check this diagram of joins http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlqa avatar image sqlqa commented ·
Thanks FatherJack So below is the answer after saw your Link Isnt? SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
SELECT * FROM B EXCEPT SELECT * FROM A
2 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

sqlqa avatar image sqlqa commented ·
Hi Scot Hauder, If We used SELECT * FROM TableA RIGHT OUTER JOIN TableB ON TableA.name = TableB.name WHERE TableA.id IS null Result should be 1 Rutabaga 3 Darth Vader but as you suggested EXCEPT then we get only Table B records. Its not correct one
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder commented ·
None of table A is shaded, so your query is wrong
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.