SQLTeam.com | Weblogs | Forums

Inner join partly working


#1

Hi, I have two tables, one called 2015-2016 Points and the second called fixturesEd. Up to now I've been using
select * from [fixturesEd.csv] as a
inner join [fixturesEd.csv] as b
on b.Date=a.Date
on b.Hometeam=a.Hometeam
and b.Awayteam=a.Awayteam
inner join [2015-2016 Points ] as c
on c.Teams=b.HomeTeam
inner join [2015-2016 Points ] as d
- on d.Teams=b.Awayteam
and have had no problems. The layout of the table are simply points = Date,Teams,pts,sco and fixturesEd = Date,HomeTeam,AwayTeam,hs,as. I'm creating the fixuresEd from a csv file. Whats Happening is I can inner join Teams to Hometeam but not teams to Awayteam. I've been trying one inner join for each, then both together. All teams exist in both tables and ive checked spellings. I can select awayteams from the fixture table without a problem. The strange thing is if I go into the csv file and swap the two columns around, then rebuild table, so now the table looks like date,Awayteam,HomeTeam, I can inner join the awayteam but not the hometeam. When Ive done this before with a csv file ive had no problems. Can anybody advise me what I'm missing. I have tried this on csv files saved in the past and it works perfect.
Regards Robert


#2

Your syntax appears to be incorrect. The following will parse correctly, but whether the join conditions are correct or not is something which will need further investigation.

SELECT  *
FROM    [fixturesEd.csv] AS a
        INNER JOIN [fixturesEd.csv] AS b ON 
			b.Date = a.Date
			AND b.Hometeam = a.Hometeam
			AND b.Awayteam = a.Awayteam
        INNER JOIN [2015-2016 Points ] AS c ON 
			c.Teams = b.HomeTeam
        INNER JOIN [2015-2016 Points ] AS d ON 
			d.Teams = b.Awayteam

By the way, when you name tables, naming it with trailing spaces etc. adds to the confusion.

One of our regular contributors, @robert_volk (if I am not mistaken) once had an article on how to obfuscate and confuse your enemies (aka colleagues). One of his suggestions was to use spaces in unexpected places. Unfortunately I don't seem to be able to locate the article at the moment, but if you find it, it is a hilarious read.


#3

Awwww, shucks, I'm blushing.

Not sure I wrote about spaces in names, but I talk about it in Revenge: The SQL! You can view a recording here:

Fair warning: first time doing the revised version, so it's a little rough. I've had about a year to polish it, but it's based on previous SQL Saturday presentations.

And just to make you feel better (or worse) I have a table at work named Role with a space at the end [Role ]. I guess being a reserved word wasn't enough for whoever designed it.


#4

Awesome video :+1:


#5

+1

AdventureskroW will never look the same again :smile:

@robert_volk If they stick you on a plane across the pond anytime soon let me know pls?


#6

Rob, yes, that, (or another version of it) was what I was thinking about it. But I had it in my mind as an article rather than a video. Watched it again and laughing :laughing: :laughing: :laughing: again!


#7

Well I just presented Revenge: The SQL in Oslo last weekend, and will present in Gothenburg, Sweden today:

http://www.sqlsaturday.com/433/Sessions/Schedule.aspx
http://www.sqlsaturday.com/414/Sessions/Schedule.aspx

I didn't make the cut for SQL Saturday Cambridge this year, hopefully next year I'll make it.

Keep an eye out for SQLBits next year, I usually make it over for that (missed it this year for a client migration).