Link to home
Start Free TrialLog in
Avatar of webdork
webdork

asked on

Tricky Join SQL

I've got a table of golfers that are in the 2011 US Masters.  One of the fields is qualification (how they managed to gain entrance to the tournament). I've got a table of the qualifications.  Golfers can qualify in one or more of 19 ways. So...

I need a join where all methods of qualification are returned

The tables look like this.
TblQual
QualID QualMethod
1 | Previous Winner
2 | Won British Open in last 5 years
3 | Won USOpen in last 5 years
4 | Won USPGA in last 5 years


TblPlayer
PlayerID Name Qual
1 | Lee Westwood | 1,3
2 | Ian Poulter | 2,3
3 | Martin Kaymer | 4
4 | Tiger Woods | 1,2,3,4

 
ASKER CERTIFIED SOLUTION
Avatar of Thomasian
Thomasian
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Sharath S
Sharath S
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I also think that there should be a thrid table which records the wins 'qual' with the players. the table could be as simple as this;

RecID - int (primary field)
PlayerId - int
Qual - tinyint

Each player could have either no entry or multiple entries.

This makes viewing and working with results very easy indeed.


Hope this helps.

A.
@Thomasian

I like your idea... quite complex, but complete.
Avatar of webdork
webdork

ASKER

I'm not really understanding... When I try the queries above I'm getting an error.

Syntax error converting the nvarchar value '6-B' to a column of data type int.

The column Qual in the Players Table is nvarchar because some of the qualifier codes are alpha and numeric like 6-B.

I thought it would be simpler and use the IN operator.

The results I'm looking for would be like this:

Select * from NewView where PlayerId = 4

Results:
Tiger Woods | (1) Former Masters Champion, (2) Winner of the Open Championship in the last 5 years, (r)  Winner of the US Open in the last 5 years.

etc..



>> I'm not really understanding... When I try the queries above I'm getting an error.
Which query are you referring?
>> The column Qual in the Players Table is nvarchar because some of the qualifier codes are alpha and numeric like 6-B.
I assumed that you have qualifiers separated by comma. Is that nit correct?
Avatar of webdork

ASKER

Yes quals separated by Comma. Query below.


select *
  from (
SELECT PlayerID,Name,ltrim(SUBSTRING(Qual, n, CHARINDEX(',', Qual + ',',n) - n)) AS QualID
 FROM @TblPlayer
CROSS JOIN (SELECT number FROM master..spt_values WHERE type = 'P')  AS Numbers(n)
WHERE SUBSTRING(',' + Qual, n, 1) = ','
  AND n < LEN(Qual) + 1) t1
 join @TblQual t2 on t1.QualID = t2.QualID
order by t1.PlayerID
webdork,

Have you tried my suggestions in http:#a34868246 ?

If so, are you having the same error?
The what error are you getting? I have tested this for your sample data.