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

SQL Server 2005 Forums

Transact-SQL (2005)

Left Join?

Author

Topic

WebRuss
Starting Member

2 Posts

Posted - 2009-07-14 : 17:41:17

Hello All

I have 3 tables

Table 1:
Student
*id
*name
*address

Table 2
Employer
*id
*firm_name
*web_url

Table 3
xref (This table creates the many to many relationship)
*student_id
*employer_id

Students can sign up to interview at different employers. When they do a record goes in xref table for each employer. for example
Student ID 10 wantes to interview with emplyers id 2,3,4,6

Thus the xref table will have:
10,2
10,3
10,4
10,6

I need sql that will pull all employers regardless if they have a record in the xref table, and I need to see if a certain student has selected them.

I'm close but I'm missing something. My sql so far:

SELECT e.id e.firm_name, e.web_url, x.student_id
FROM employer e
LEFT OUTER JOIN xref x on e.id = x.employer_id

How do I get all employers and see which ones the student selected?

Regards
Russ

tp
Starting Member

5 Posts

Posted - 2009-07-14 : 18:56:15

Hi Russ,

Your query fetches exactly what you want. It fetches all the employers from the employer table and the data for the students.

Can you be more clear on what you want?

Thanks,
tp

WebRuss
Starting Member

2 Posts

Posted - 2009-07-15 : 09:18:10

Thanks for your reply. I apologize if I was unclear.

The xref table creates a many to many relationship between students and employers and will include many student ID's for example:

xref
-----------
*student_id
*employer_id

10,2
10,3
10,4
11,2
11,4
12,5
12,3

When I run the select as it is now I get duplicate employer rows because there is more than one student ID in the xref table.

I'd like to add a WHERE x.student_id = 10 but then I only get the employers rows that have an entry in the xref table and not ALL rows from the employers table even if the student has not selected it.

This is an old asp classic site where I need to show all the employers and a check mark next to the employers that the student selected. I need to see all employers, and some field that tells me which rows a specific student selected without duplicates.

I think I can do this a 2 queries, I just want to be fancy and do it in one. :-)

Regards
Russ

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-07-15 : 11:35:29

[code]SELECT e.id e.firm_name, e.web_url, x.student_id
FROM employer e
LEFT JOIN xref x
ON e.id = x.employer_id
-- This needs to be in the ON clause, not the WHERE clause.
AND x.student_id = 10
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-15 : 13:08:36

also see this
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/60205.aspx

to make it more clear,


SELECT e.id e.firm_name, e.web_url, x.student_id ,
CASE WHEN x.student_id IS NOT NULL THEN 'Selected' ELSE 'Not Selected' END AS Status
FROM employer e 
LEFT JOIN xref x
ON e.id = x.employer_id
AND x.student_id = 10

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources