SQL Server 2000 - WHERE xx IN [table]

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

Transact-SQL (2000)

SQL Server 2000 - WHERE xx IN [table]

Author

Topic

bsausser
Starting Member

2 Posts

Posted - 2006-02-03 : 19:37:46

I have broke this Query down to it's simplest form. It does not return any results:
SELECT
idItem,
subjectItem,
qntyItem,
chrgItem,
idClient

FROM Items

WHERE idItem IN
(
SELECT idItem
FROM ItemsAppointmentRel
WHERE idApt = '0BF7A533-8590-4D47-B4FB-5ECFE68A501B'
)

However:
SELECT idItem
FROM ItemsAppointmentRel
WHERE idApt = '0BF7A533-8590-4D47-B4FB-5ECFE68A501B'
does return the two records I have in the relation table

[0] f3cc77ff-8400-41d5-9e4e-b62afc315a81
[1] 8a7641cd-8be7-4363-94a2-24f4377029f1

Without the WHERE IN: it returns all records.

Is there something wrong with my syntax? Or is this only available in SQL 2005?

Regards,
b-

nosepicker
Constraint Violating Yak Guru

366 Posts

Posted - 2006-02-03 : 20:10:30

What happens if you run this:

SELECT
idItem,
subjectItem,
qntyItem,
chrgItem,
idClient
FROM Items
WHERE idItem = 'f3cc77ff-8400-41d5-9e4e-b62afc315a81'

SELECT
idItem,
subjectItem,
qntyItem,
chrgItem,
idClient
FROM Items
WHERE idItem = '8a7641cd-8be7-4363-94a2-24f4377029f1'

If you get no results with either query, well, there's your problem.

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-02-03 : 20:46:22

I vote for nosepickers suggestion, it sounds like the ITEMS table doesn't have the matching IDitem values. Instead of doing the sub-select, consider just JOINING the tables directly. Sometimes the optimizer can do the same thing both ways, but some times the sub-selects are performance killers but the JOIN resolves to a much faster execution: [CODE]SELECT i.idItem, i.subjectItem, i.qntyItem, i.chrgItem, i.idClient
FROM Items i INNER JOIN ItemsAppointmentRel iar on i.idItem = iar.idItem and iar.idApt = '0BF7A533-8590-4D47-B4FB-5ECFE68A501B'[/CODE]

bsausser
Starting Member

2 Posts

Posted - 2006-02-04 : 12:21:03

1) the idItem is in both the items table and in the ItemAptRel table.

2) What I'm trying to do is get all idItem records from the rel table that the idApt matches a parameter i send it.

I would like to use the WHERE IN statement, and not use the JOIN. I'm stumpted by the syntax of the WHERE IN -- it should work, eh?

So can anyone tell me what is wrong with the syntax? I'm pretty sure I'm screwing something up, unless 2000 is not letting me use a select statement as the object of the WHERE IN.

b-

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 12:33:08

Don't think it would cause a problem in this case but always worth getting rid of nulls if they are allowed
WHERE idItem IN
(
SELECT idItem
FROM ItemsAppointmentRel
WHERE idApt = '0BF7A533-8590-4D47-B4FB-5ECFE68A501B'
where idTtem is not null
)

Other than that it must be due to a difference in the idItem in the two tables. Do they have the same datatype (length, unicode). Do they have any trailling white spaces or control characters?

try
WHERE left(idItem, 10) IN
(
SELECT left(idItem, 10)
FROM ItemsAppointmentRel
WHERE idApt = '0BF7A533-8590-4D47-B4FB-5ECFE68A501B'
where idTtem is not null
)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-04 : 12:53:17

Try to use aliases:

Select
  i.idItem,
  i.subjectItem,
  i.qntyItem,
  i.chrgItem,
  i.idClient
from Item i
where i.idItem in
(
  select a.idItem
    from ItemsAppointmentRel a
    where a.idApt = '0BF7A533-8590-4D47-B4FB-5ECFE68A501B'
)

That might be the issue here.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-04 : 12:57:24

Nope - the objects in the subquery aren't exposed to the oouter query.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-04 : 13:54:27

Yes, but subquery may include objects from the outer query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources