August 2, 2013 at 2:18 pm
What do you mean by a staring point of a Join?
I am creating a join with some tables and I have been asked table A to be starting point for all other joins.
Now what does that mean?
August 2, 2013 at 2:34 pm
sharonsql2013 (8/2/2013)
What do you mean by a staring point of a Join?I am creating a join with some tables and I have been asked table A to be starting point for all other joins.
Now what does that mean?
I see this is a double question.
Answering the first part, the staring point is when join refuses to work and you proceed to stare at it until it starts behaving as expected
Answering the second part, your boss means he/she wants table A to be the "driving table" on the join.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2013 at 2:34 pm
I'm assuming that meant that table A is the table referred to right after FROM.
Example:
SELECT .......
FROM TableA
... JOIN TableB ON ...
... JOIN TableC ON ...
etc.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 2, 2013 at 2:38 pm
Great.
Any clue how that will work in crystal?
August 2, 2013 at 2:42 pm
sharonsql2013 (8/2/2013)
Any clue how that will work in crystal?
Not the slightest clue. Any Crystal Reports forums around?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 2, 2013 at 3:41 pm
sharonsql2013 (8/2/2013)
Great.Any clue how that will work in crystal?
Crystal accepts a command per select. If you have multiple select statements in a custom crystal command it will only read the first select through to the bottom of that inclusive statement.
Now, crystal WILL accept joins. It'll accept an entire dynamic variable select statement with multiple sets etc. HOWEVER, Crystal will create its own links. After you do your select statement you need to click over to the 'Links' tab and then look at how the tables are being joined on each other. Check to make sure that crystal didn't create some sort of goofy table join system... You can also right click the links themselves and go to options and see the exact SQL join and modify it.
Crystal is frustrating in that it tries to compensate too many things for you. You'll figure that out over time as you use crystal... trust me :crazy:
August 2, 2013 at 3:54 pm
sharonsql2013 (8/2/2013)
What do you mean by a staring point of a Join?I am creating a join with some tables and I have been asked table A to be starting point for all other joins.
Now what does that mean?
By "starting point of a join" you are talking about the what is more commonly referred to as the Left table. A JOIN table operator (INNER, OUTER, CROSS) operates on two tables; the table on the left of the JOIN operator is the Left Table, the table on the right of the JOIN operators is the Right Table. In the example below table_a is the Left Table and table_b is the right table.
SELECT a_id, b_id FROM table_a INNER JOIN table_b ON a_id=b_id
Understanding your Left & Right tables is important for understanding LEFT and RIGHT OUTER JOINS.
Take the sample code below:
USE tempdb;
CREATE TABLE table_a (a_id int);
CREATE TABLE table_b (b_id int);
INSERT INTO table_a VALUES (1),(2),(5);
INSERT INTO table_b VALUES (1),(2),(10);
... and say you needed this result:
a_idb_id
----------
11
22
NULL10
This would get you the answer that you want:
SELECT a_id, b_id
FROM table_b
LEFT JOIN table_a ON a_id=b_id
...but, because you've been asked to make "table A to be starting point for all other joins" you would have to make table A the Left table like so:
SELECT a_id, b_id
FROM table_a
RIGHT JOIN table_b ON a_id=b_id
If you can, check out the chapter about joins in Itzek Ben Gan's Microsoft SQL Server 2012 T-SQL Fundamentals; it's a great read (and totally relevant even if you are not using SQL 2012).
-- Itzik Ben-Gan 2001
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply