find the same values from one table in one colum

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

find the same values from one table in one colum

Author

Topic

olusia
Starting Member

3 Posts

Posted - 2010-07-05 : 23:49:41

Hi -

I have a really hard time finding an answer out there so I'd appreciate any help. Here's what I have:

SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem
WHERE lngTransactionID = '1233'

The results that I get from that are:

lngItemID lngTransactionID dateOrder lngEntitleItemIDRef
1233 1233 7/1/10 1233
1234 1233 7/1/10 1234

When the transaction is performed (ticket is being issued) there's a number assigned to the lngEntitleItemIDRef. When the ticket is scanned again the lngEntitleItemIDRef has the same value assigned. I need to be able to find all the same lngEntitleItemIDRef that are associated with the lngTransactionID. Here's what I would like the results to look like:

lngItemID lngTransactionID dateOrder lngEntitleItemIDRef
1233 1233 7/1/10 1233
1234 1233 7/1/10 1234
8910 8910 7/2/10 1233
8911 8910 7/2/10 1234

Thank you!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-06 : 00:11:33

quote:

SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem
WHERE lngTransactionID = '1234'

The results that I get from that are:

lngItemID lngTransactionID dateOrder lngEntitleItemIDRef
1233 1233 7/1/10 1233
1234 1233 7/1/10 1234

Your query should be " WHERE lngTransactionID = '1233' " ?

quote:
When the transaction is performed (ticket is being issued) there's a number assigned to the lngEntitleItemIDRef. When the ticket is scanned again the lngEntitleItemIDRef has the same value assigned. I need to be able to find all the same lngEntitleItemIDRef that are associated with the lngTransactionID. Here's what I would like the results to look like:

lngItemID lngTransactionID dateOrder lngEntitleItemIDRef
1233 1233 7/1/10 1233
1234 1233 7/1/10 1234
8910 8910 7/2/10 1233
8911 8910 7/2/10 1234

this ?

SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem
WHERE lngTransactionID = '1233'
or    lngEntitleItemIDRef = '1233'

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

olusia
Starting Member

3 Posts

Posted - 2010-07-06 : 00:27:59

Thank you khtan!

I have corrected my first query as I should be asking for an lngTransactionID = '1233'.

I'm actually looking for something that will allow me to get all the lngEntitleItemIDRef associated with the first lngTransactionID. Basically I run the query in two parts right now. The first part is:

SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem
WHERE lngTransactionID = '1233'

The second part is:

SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem
WHERE lngEntitleItemIDRef in (1233, 1234)

This way I get my results:

lngItemID lngTransactionID dateOrder lngEntitleItemIDRef
1233 1233 7/1/10 1233
1234 1233 7/1/10 1234
8910 8910 7/2/10 1233
8911 8910 7/2/10 1234

I'm wondering if there's any way to combine those two steps knowing that I only know that lngTransactionID = '1233'.

jessiefun
Starting Member

35 Posts

Posted - 2010-07-06 : 00:56:51

SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem
WHERE lngTransactionID = '1233' or lngEntitleItemIDRef in
(SELECT lngEntitleItemIDRef FROM tblItem WHERE lngTransactionID = '1233' )

I think this should work~~

Regards,
Jessie

olusia
Starting Member

3 Posts

Posted - 2010-07-06 : 01:01:48

Beautiful! Thank you so much for your help!

jessiefun
Starting Member

35 Posts

Posted - 2010-07-06 : 01:08:28

Glad I could help.

Jessie

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-06 : 01:09:14

[code]
SELECT lngItemID, lngTranactionID, dateOrder, lngEntitleItemIDRef
FROM tblItem i
WHERE lngTransactionIDin in (1233, 1234)
OR IngEntitleItemIDRef in (1233, 1234)
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources