=* conversion to right join with multple tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

=* conversion to right join with multple tables

Author

Topic

dbaerwald
Starting Member

5 Posts

Posted - 2007-11-06 : 16:46:45

I need to find a way to convert this old style join using =* to the SQL 2005 standard of using a right join.

Here is the original query:

Select na.proper_name, na.name, first_name,middle_initial,last_name,address_2, Address_3,np.Expiration_date, Expiration_date_text
from
ongoing..nominee_info ni,
ongoing..nominee_positions np,
ongoing..agency_names na,
ongoing..nominee_text nt
Where
ni.nominee_id = 727
and np.nominee_id = ni.nominee_id
and np.agency_id = na.agency_id
and np.received_date > '11/01/2006'
and nt.agency_id =* na.agency_id
and nt.position_id =* np.position_id
and nt.nominee_id =* ni.nominee_id

It returns one record.

My attempt to convert to right joins:

Select na.proper_name, na.name, first_name, middle_initial, last_name, address_2, Address_3, np.Expiration_date, Expiration_date_text, np.received_date
from
ongoing..nominee_text nt
right join
ongoing..agency_names na
ON
nt.agency_id = na.agency_id
right join
ongoing..nominee_positions np
ON
nt.position_id = np.position_id
right join ongoing..nominee_info ni
ON
nt.nominee_id = ni.nominee_id
Where
ni.nominee_id = 727
and np.nominee_id = ni.nominee_id
and np.agency_id = na.agency_id
and np.received_date > '11/01/2006'

This query produces no records. I have tested these queries on SQL 2000 and 2005 using compatability 8. Same results.

Any ideas what's wrong with the converted query? Thanks very much for any assistance or info you can provide.

David

Also I found a query conversion utility on the web and it yielded about the same query that I created. Here is the link to the utility demo if anybody is interested.

http://www.swissql.com/products/sql-translator/sql-converter.html

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 17:26:08

[code]Select na.proper_name,
na.name,
first_name,
middle_initial,
last_name,
address_2,
Address_3,
np.Expiration_date,
Expiration_date_text
from ongoing..nominee_info as ni
inner join ongoing..nominee_positions as np on np.nominee_id = ni.nominee_id
inner join ongoing..agency_names as na on na.agency_id = np.agency_id
right join ongoing..nominee_text as nt on nt.agency_id = na.agency_id
and nt.position_id = np.position_id
and nt.nominee_id = ni.nominee_id
Where ni.nominee_id = 727
and np.received_date > '11/01/2006'[/code]

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-11-06 : 17:30:52

I strongly recommend that you use LEFT JOINs - the human brain is not good at understanding ~~LEFT~~ RIGHT JOINs.

Does

and nt.agency_id =* na.agency_id

mean that you must have nt.agency_id or must have na.agency_id?

given this code that you have "mapped" this comparison to:

right join ongoing..agency_names na
ON nt.agency_id = na.agency_id

If you must have ongoing..nominee_text (and ongoing..agency_names is optional) then you need a LEFT JOIN, if its the other way round then you need a RIGHT JOIN.

Part of your problem is that you have "required" conditions in your WHERE clause which refer to tables that are part of your Outer Join syntax.

You have:

WHERE ...
AND np.agency_id = na.agency_id

but NP and NA are the subject of Outer Joins. This equals test will require that the value of both is NOT NULL, whereas the Outer Join infers that one, or both, may be absent - and thus NULL.

Having these as Outer Join AND part of the Where clause is therefore mutually exclusive.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 17:34:01

quote:
Originally posted by Kristen

I strongly recommend that you use LEFT JOINs - the human brain is not good at understanding ~~LEFT~~ RIGHT JOINs.

E 12°55'05.25"
N 56°04'39.16"

dbaerwald
Starting Member

5 Posts

Posted - 2007-11-06 : 18:06:05

Thanks for the responses.

quote:
Originally posted by Kristen:

If you must have ongoing..nominee_text (and ongoing..agency_names is optional) then you need a LEFT JOIN, if its the other way round then you need a RIGHT JOIN.

For this query the only table not needed is the ongoing..nominee_text table. That's why I was using the right join. If I tried to change to a left join the query becomes ugly fast.

Are not right joins and left joins the same thing just pending the location of the operators?

i.e., Table A left join Table B is the same as
Table B right join Table A?
(if this is not the case then I am looking at these joins incorrectly)

Peso - I tried your arrangement of the query. Its cleaner looking, but still yields no records.

quote:
Originally posted by Kristen

Part of your problem is that you have "required" conditions in your WHERE clause which refer to tables that are part of your Outer Join syntax.

For the "required" conditions in the WHERE clause, I thought only the ones relating to the table "not needed", in this case ongoing..nominee_text, are required as part of the ON clause?

Thanks again for the help!!!! This query seemed simple at first.

Kristen
Test

22859 Posts

Posted - 2007-11-07 : 01:34:32

"If I tried to change to a left join the query becomes ugly fast"

I don't see why, as you correctly say its just a case of rearranging the order of the tables. Its the same thing, just expressed in a way that humans can more easily read.

"I thought only the ones relating to the table "not needed", in this case ongoing..nominee_text, are required as part of the ON clause?"

You had all the tables Right Joined. That implies that the data on the Left of the join is optional, the data on the Right is required. So anything in the WHERE clause relating to the the left of a join, which itself is the subject of a Right Join, will make it required - i.e. effectively an INNER JOIN.

So you will get what Peso has shown.

And you say that "the only table not needed is the ongoing..nominee_text table", which matches what Peso has written.

So why, in the original Where clause, are there "=*" on the other tables?

Change

and np.received_date > '11/01/2006'

to

and np.received_date > '20061101'

in case default date format on SQL 2005 server is different?

Kristen

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-11-07 : 08:29:14

quote:
I need to find a way to convert this old style join using =* to the SQL 2005 standard of using a right join.

This is actually a left join as the order of na, np and ni is before nt in the from clause. (*= does NOT mean left join and =* does NOT mean right join. The * is against the table where all the rows are to be kept.)

For simple queries like this, ie queries without derived tables and subqueries, you can cheat if you have access to Enterprise Manager by:

1. Right click on any table, Open Table, Return All Rows.
2. Click the SQL button to show the SQL pane.
3. Paste in your query and click the Run Button.
4. The query will be reformatted even if the query fails.

Kristen
Test

22859 Posts

Posted - 2007-11-07 : 09:02:13

That's useful to know ...

That gives


SELECT	na.proper_name, na.name, first_name, middle_initial, last_name, 
	address_2, Address_3, np.Expiration_date, Expiration_date_text
FROM	ongoing..nominee_info ni 
	INNER JOIN ongoing..nominee_positions np 
		ON ni.nominee_id = np.nominee_id 
	INNER JOIN ongoing..agency_names na 
		ON np.agency_id = na.agency_id 
	LEFT OUTER JOIN ongoing..nominee_text nt 
		 ON na.agency_id = nt.agency_id 
		AND np.position_id = nt.position_id 
		AND ni.nominee_id = nt.nominee_id
WHERE	    (ni.nominee_id = 727) 
	AND (np.received_date > '20061101')

(assuming your date is 1st Nov 2006, not 11th January)

Kristen

dbaerwald
Starting Member

5 Posts

Posted - 2007-11-07 : 11:42:24

Thank you for all the great input. It helps alot to have more background info about these joins (left and right). I'm pretty new at working with them.

The query that you posted Kristen does return the correct row. I was thinking about the left join incorrectly and that is why I thought that the query would be ugly. I didn't realize you could do this:

LEFT OUTER JOIN ongoing..nominee_text nt
ON na.agency_id = nt.agency_id
AND np.position_id = nt.position_id
AND ni.nominee_id = nt.nominee_id

Thanks again for all of the assistance!!!! I very much appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources