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

query problem

Author

Topic

Alina
Starting Member

20 Posts

Posted - 2007-11-06 : 05:06:43

Hello. I'm learning sql and I'm running the following query and getting 0 rows affected as a result, when I know i have 3 rows that should come up. can someone please tell me what i'm doing wrong?
thank you very much

select stove.serialnumber, invoice.invoicenbr,invoice.invoicedt from stove,invoice where stove.fk_empid=invoice.fk_empid and stove.fk_empid in (select fk_empid from invoice WHERE month(invoicedt) between '05' and '05' and year(invoicedt) between 2002 and 2002)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-06 : 05:20:34

[code]select s.serialnumber, i.invoicenbr, i.invoicedt
from stove s Join invoice i on s.fk_empid=i.fk_empid
where i.invoicedt > '20020430' and i.invoicedt < '20020630'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Alina
Starting Member

20 Posts

Posted - 2007-11-06 : 05:47:18

thank you for the reply harsh_athalye, but running the query using join versus subqueries did not change anything. if i run just the sub query:
select fk_empid from invoice WHERE month(invoicedt) between '05' and '05' and year(invoicedt) between 2002 and 2002
I get 3 rows as the result. basically all I am trying is to add tho those results the stove.serialnumber that matches the results of those queries. i don't see why it does not show me 3 rows

Alina

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 05:55:37

[code]select stove.serialnumber,
invoice.invoicenbr,
invoice.invoicedt
from stove
inner join invoice on invoice.fk_empid = stove.fk_empid
where exists (select * from invoice AS c WHERE c.invoicedt >= '20020501' AND c.invoicedt < '20020601' and stove.fk_empid = c.fk_empid)[/code]

E 12°55'05.25"
N 56°04'39.16"

arorarahul.0688
Posting Yak Master

125 Posts

Posted - 2007-11-06 : 08:01:47

try this

select stove.serialnumber, invoice.invoicenbr,invoice.invoicedt from stove inner join
(select fk_empid from invoice
WHERE month(invoicedt) between '05' and '05' and year(invoicedt) between 2002 and 2002) n
on stove.fk_empid=n.fk_empid

if u give ur tables hint it can be done easily

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-06 : 08:09:10

quote:
Originally posted by arorarahul.0688

try this

select stove.serialnumber, invoice.invoicenbr,invoice.invoicedt from stove inner join
(select fk_empid from invoice
WHERE month(invoicedt) between '05' and '05' and year(invoicedt) between 2002 and 2002) n
on stove.fk_empid=n.fk_empid

if u give ur tables hint it can be done easily

Rahul Arora
MCA 07 Batch
NCCE Israna, Panipat
HRY, INDIA

######################
IMPOSSIBLE = I+M+POSSIBLE

Isnt

month(invoicedt) between '05' and '05'

same as

month(invoicedt)=5

1 Why are you treating number as char?
2 Are you actually reading the replies before you reply?

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 08:12:07

quote:
Originally posted by Alina

can someone please tell me what i'm doing wrong?

The IN operator has some advantages. One of them are the skill to pick column from outer query.
ALWAYS ALWAYS prefix your column names when dealing with more than 1 table in a query.

Or try the suggestion made 11/06/2007 : 05:55:37

E 12°55'05.25"
N 56°04'39.16"

Alina
Starting Member

20 Posts

Posted - 2007-11-06 : 08:14:57

thank you guys; all of those queries are just another version of my query, but none of those display any results. that's my problem. i do not understand why they don't show the 3 results that i know exist. if i run just the subquery on its own i get the 3 answers, and it makes no sense to me as to why i don't see the answers when i run the entire query. i must be missing something, i just don't know what.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-06 : 08:19:46

All you have proven is that there are three fk_empid in INVOICE table!
Then you are JOINING that result to STOVE table. Are the three fk_empid's present in STOVE table?

E 12°55'05.25"
N 56°04'39.16"

Alina
Starting Member

20 Posts

Posted - 2007-11-06 : 08:44:23

You're right Peso, my mistake and I am sorry. The empid do not match that's why i get zero results to the query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources