JOIN troubles - 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 2000 Forums

SQL Server Development (2000)

JOIN troubles

Author

Topic

stevil
Starting Member

3 Posts

Posted - 2006-01-30 : 19:40:01

I'm currently working on a query which has me pulling my hair out and was wondering if anyone could help me out a little. What I have is 3 tables (only 3 involved anyways) one table I use to store dates, another stores host names, another stores how many referrals we get from them (its affialte tracking). I have it all working ok now an the saved data is coming back correctly but now people want to see the blank days in the data aswell. Originally I thought "Easy, just a LEFT/RIGHT join an job done" but the problem is I have to show an individual host so in the WHERE clause it has a condition saying what the host should be. Because of the fact there is no host for that day it fails the condition an doesn't return the correct data. Below is an example of what I mean.


SELECT 
	*
FROM 
	dates
		LEFT JOIN 
	refs 
		ON 
			dates.date_id = refs.date_id
		INNER JOIN 
	hosts 
		ON 
	hosts.host_id = refs.host_id
WHERE 
	refs.host_id = '58'
GROUP BY 
	refs.date_id

I'm not sure if I've explained myself to well there. If it doesn't make sense please let me know an I'll try again.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-01-30 : 19:50:07

can you post some sample data & expected result ?

----------------------------------
'KH'

stevil
Starting Member

3 Posts

Posted - 2006-01-30 : 20:18:44

[code]
Table dates
date_id date_date
--------------------------
1 2006-01-10
2 2006-01-11
3 2006-01-12

Table hosts
host_id host_name
-------------------------
1 mock.com
2 fake.com
3 silly.com

Table refs
date_id host_id ref_cnt
---------------------------------------
1 1 3
1 3 7
2 1 4
2 2 3
2 3 2
3 1 1
3 3 4

desired result (searching for host 2)
date_date host_id ref_cnt
---------------------------------------
2006-01-10 2 0
2006-01-11 2 3
2006-01-10 2 0
[/code]

Thanks

stevil
Starting Member

3 Posts

Posted - 2006-01-30 : 20:50:53

Its ok, I got it figured out. Work on it for hours an then post it an 10 minutes later I solve, always has to happen like that doesn't it? For anyone else who has this problem the solution is


SELECT 
	*
FROM 
	dates
		LEFT JOIN 
	refs 
		ON 
			dates.date_id = refs.date_id
		INNER JOIN 
	hosts 
		ON 
	hosts.host_id = refs.host_id AND res.host_id = 2

I'm not sure if that exact syntax will work with what I posted as its all cut down versions to make it easier to read. You can see how its done tho. Thanks for your time an help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-30 : 20:52:44

you should not apply criteria on the outer table in a LEFT OUTER JOIN.

See http://www.sqlteam.com/item.asp?ItemID=11122 for more details and a solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources