Sql query - 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

Sql query

Author

Topic

Jikol
Starting Member

13 Posts

Posted - 2008-07-01 : 09:42:12

Hey All,

I'm having trouble with a query. I have two tables, Problems(Problem(PK), Grade, Boulder) and Data(Problem(FK), Date, Climber).

The Data table is where I store climbs(problem) that a climber has completed. I can easily write a query that shows what problems a climber has completed(basically just the data Table), But I would like to query the problems a climber has not yet completed. i.e. compare the data table to see what the climber has done and subtract those climbs from the problem table and then show the rest.

I hope that this makes sense.

Thanks,

Dylan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 09:46:06

use LEFT JOIN


select *
from   Problems p left join Data d on p.Problem = d.Problem
where  d.Problem is null

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 13:00:11

or:-

select *
from   Problems p 
WHERE p.Problem NOT IN(SELECT Problem from Data )

Jikol
Starting Member

13 Posts

Posted - 2008-07-02 : 15:05:38

Hey again.

I sort of have it working, but found another problem. Here is what I got
[code]

SELECT dbo.Problems.Stars, dbo.Problems.Grade, dbo.Problems.Boulder, dbo.Problems.PageNum, dbo.Data.Climber, dbo.Problems.Problem
FROM dbo.Problems INNER JOIN
dbo.Data ON dbo.Problems.Problem <> dbo.Data.Problem
[code]

This might be a little hard to explain but here goes.

The above works when the data table contains only one record for the climber. When the data table gets another record for the climber things get messed up. What it's doing is for each record in the data table it performs the comparison so when the climber has two records it performs the comparison individually for each record.

Hope this makes sense.

Thanks

Jikol
Starting Member

13 Posts

Posted - 2008-07-02 : 18:43:16

I think I have a better way to explain this.

The problems table has climbs 1-10.

Lets say that Climber A climbs problems 1,2,3,4,5

So now the data table has records for each of these climbs.

What the query should do is so that 6-10 has not been climbed.

What it is doing is comparing the first record (climb 1) and reporting climb 2-10 not climbed, then it looks at record 2 and reports that climbs 1,3-10 not climbed, etc.

contrari4n
Starting Member

27 Posts

Posted - 2008-07-02 : 19:36:58

2 comments.

1) Your question has already been answered by khtan and visakh16
2) NEVER use <> in a join. Apart from the fact it is wrong, you will end up with the worst performing query ever. You may not notice this with just a few rows, but add a few thousand and you could make a cup of coffee (and grow the coffee beans first) while it runs!

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

Jikol
Starting Member

13 Posts

Posted - 2008-07-02 : 19:56:09

contrari4n,

I had actually tried the others suggestions and could not get them to work. So instead I kept researching how to accomplish this. I came up with what I had posted. I got most of it to work, but when I tried to put in more than one record for a person it didn't work as I thought. I then again tried several others things and still no luck. I then came back to the forum for more help.

The forum is for new sql users which I am and still trying to learn. I appologize if I didn't understand the responses and needed more help.

I'll try somewhere else for help.
T

Thanks,

Dylan

contrari4n
Starting Member

27 Posts

Posted - 2008-07-02 : 20:21:15

I apologise for the flippant reply. I have just re-read your problem and it looks a little more complicated than I first thought.

Am I right in thinking you want all climbs not completed by each climber? In that case, assuming a table called Climber(Climber(PK), Name) try this:

CREATE TABLE Problem(Problem int primary key, Grade int)

CREATE TABLE Data(Climb int primary key, Problem int, Climber int)

CREATE TABLE Climber(Climber int primary key, Name varchar(50))

INSERT Climber
SELECT 1, 'John'
UNION
SELECT 2, 'Fred'
UNION
SELECT 3, 'Sue'

INSERT Problem
SELECT 1, 1
UNION
SELECT 2, 1
UNION
SELECT 3, 2
UNION
SELECT 4, 2
UNION
SELECT 5, 3

INSERT Data
SELECT 1, 1, 1
UNION
SELECT 2, 1, 2
UNION
SELECT 3, 2, 2
UNION
SELECT 4, 3, 3

SELECT c.Name, p.Problem, p.Grade
FROM dbo.Climber AS c
CROSS JOIN dbo.Problem p
LEFT JOIN dbo.Data d ON c.Climber = d.Climber AND p.Problem = d.Problem
WHERE d.Problem IS NULL
--AND c.Climber = 2

The likelihood is that you could end up with a huge resultset and poor performance, so restrict it to a particular climber by uncommenting the last line.

This may not be the most efficient solution, but should get the results you want.

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

Jikol
Starting Member

13 Posts

Posted - 2008-07-02 : 20:25:25

Thank you for the response. I thought that the issue of climbs not climbed by each climber was causing me the issue, but I didn't really know how to verbalize it.

I will give your solution a try and report back.

Thanks again!!

Dylan

contrari4n
Starting Member

27 Posts

Posted - 2008-07-03 : 06:19:47

My last reply was totally wrong. That'll teach me to post in a hurry without thinking it through.

I have edited the reply, rather than repeat the code here.

Hopefully that has helped.

Richard Fryar
http://www.sql-server-pro.com
SQL Server Articles and Tips

Jikol
Starting Member

13 Posts

Posted - 2008-07-03 : 09:50:01

Wow I thought the first one was confusing. I got the first bit of code to so sort of work. When I fixed the climbers table name it gave me the climbers that had climbed nothing at all (Null). The tables that I'm searching I don't believe would contain null values and I don't think I can query this way (explain down later). Also the info that I need is in just two table, Data and Problems.

Here is what I have so far.

Tables
Problems(Problem(PK), Grade, Boulder, stars)
Data(Problem(FK), Date, Climber).
Climbers(climber(PK)

What I'm trying to do is just use the Problems and Data table to find what climbers have not climbed what problems. Again I'm way new at this but the data table will not have a null value in it, every record will have a 3 values, problem, data and climber.

So if I have problems 1-10 in the problems table and climbers A&B are the only ones to have records in the data table and.....

Climber A has done 1,2,3,4,5
Climber B has done 2,4,6,8

The query will return
Climber A has not done 6,7,8,9,10
Climber B has not done 1,3,5,7,9.

If a climber has not done any climbs I don't need it to list all the problems for him/her so this is why I don't need the climbers table in the query (I think).

I am going to use your suggestion about filtering per climber using a drop down list and parameterized query.

Thanks for your help on this and patience

Jikol
Starting Member

13 Posts

Posted - 2008-07-04 : 10:27:30

contrari4n,

I got your last solution to work.

Thank you so much for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources