left join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

left join

Author

Topic

lallejoe
Starting Member

5 Posts

Posted - 2011-02-24 : 08:52:04

Hy

I've got the following 4 Tables and Fields:

- numplan (pattern, fkcss, pkid)
- css (pkid, name)
- linemap (fknumplan, fkpickup)
- pickup (name, pkid)

A numplan must not have a css or a pickupgroup entry. To link the table pickup with the table numplan, you have to go over the table linemap. I made the following query:

select numplan.pattern, css.name, pickup.name from numplan left join css on numplan.fkcss = css.pkid where numplan.pkid = linemap.fknumplan and linemap.fkpickup = pickup.pkid

This query works but shows me only all fields whitch have a pickup entry. But this entry can be NULL.

Thanks for your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-02-24 : 08:59:28

Try this

select numplan.pattern, css.name, pickup.name from numplan left join css on numplan.fkcss = css.pkid and linemap.fkpickup = pickup.pkid where numplan.pkid = linemap.fknumplan

Madhivanan

Failing to plan is Planning to fail

lallejoe
Starting Member

5 Posts

Posted - 2011-02-24 : 10:26:10

Hey madhivanan

thanks for your fast reply. I made it like this, but it doesn't work.
I got an error like "An ON clause has an invalid table reference"

I already checked the names...they're correctly written.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 10:36:56

post the your query which gave you this error

Cheers
MIK

lallejoe
Starting Member

5 Posts

Posted - 2011-02-24 : 10:53:25

select
numplan.pattern, css.name, pickup.name from
linemap, pickup, numplan left join css on numplan.fkcss = css.pkid and linemap.fkpickup = pickup.pkid
where numplan.pkid = pickupgrouplinemap.fknumplan_line

This is an example with the same problem. My real query (with more tables), which i need to get Data from a Cisco Unified Call-Manager Database is:

select
device.name as devicename, device.description as devicedescription, typemodel.name as phonetyp, enduser.userid, numplan.dnorpattern as number, numplan.description as numberdescription, callingsearchspace.name as cssname, pickupgroup.name as pickupgroup from
devicenumplanmap, device left join typemodel on device.tkmodel = typemodel.enum left join enduser on device.fkenduser = enduser.pkid, pickupgrouplinemap, pickupgroup, numplan left join callingsearchspace on numplan.fkcallingsearchspace_sharedlineappear = callingsearchspace.pkid and pickupgrouplinemap.fkpickupgroup = pickupgroup.pkid
where
device.pkid = devicenumplanmap.fkdevice and numplan.pkid = devicenumplanmap.fknumplan and numplan.pkid = pickupgrouplinemap.fknumplan_line

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-24 : 12:12:35

The way you wrote Join Syntax is Wrong................................

Kindly Read Books Online and check the Syntax of Join in SQL server OR

http://dotnetslackers.com/articles/sql/SQL-SERVER-JOINs.aspx

Cheers
MIK

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-02-24 : 12:25:51

You are mixing old (very old) syntax with the ANSI standard JOIN syntax.

This is what (I think) your query translates to:



SELECT 
	device.name AS devicename
	, device.description AS devicedescription
	, typemodel.name AS phonetyp
	, enduser.userid
	, numplan.dnorpattern AS number
	, numplan.description AS numberdescription
	, callingsearchspace.name AS cssname
	, pickupgroup.name AS pickupgroup

FROM 
	devicenumplanmap
	INNER JOIN device ON device.pkid = devicenumplanmap.fkdevice  -- device.pkid = devicenumplanmap.fkdevice
	INNER JOIN numplan ON  numplan.pkid = devicenumplanmap.fknumplan -- AND numplan.pkid = devicenumplanmap.fknumplan
	INNER JOIN pickupgrouplinemap ON numplan.pkid = pickupgrouplinemap.fknumplan_line -- AND numplan.pkid = pickupgrouplinemap.fknumplan_line
	INNER JOIN pickupgroup ON pickupgrouplinemap.fkpickupgroup = pickupgroup.pkid -- AND pickupgrouplinemap.fkpickupgroup = pickupgroup.pkid

	LEFT JOIN typemodel ON device.tkmodel = typemodel.enum
	LEFT JOIN enduser ON device.fkenduser = enduser.pkid	
	LEFT JOIN callingsearchspace ON numplan.fkcallingsearchspace_sharedlineappear = callingsearchspace.pkid

~~But I've got no clue what predicates [pickupgroup] should join on~~

EDIT :: I think your problem was that you'd mixed the LEFT JOIN condition for callingsearchspace with the old skool INNER JOIN for pickupgroup

GET USED TO THE ANSI SYNTAX -- You'll be glad you did eventually.

If that doesn't help then you need to give us some sample data and expected results.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

lallejoe
Starting Member

5 Posts

Posted - 2011-02-25 : 03:27:36

Hey Charlie

Thanks for your help. It works!
Have a nice weekend!

Greez

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources