Sql Select query

I am trying to select from a table where lngDataid = 145 and lngdataid = 1989.  The visitid is the same 1234 for both records but the lngdataid in store in seperate rows as the an clause will not work.

How can I select records that have the same visitid and have lngdataid's of 145 and 1989 but not in the same record.

Thanks
running32Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
Something like this:
SELECT lngvisitid
FROM dbo.YourTable
WHERE lngDataid IN (145, 1989)
GROUP BY lngvisitid
HAVING COUNT(*) = 2

Open in new window

0
 
jimtpowersCommented:
SELECT *
FROM   table
WHERE (lngDataid IN (145, 1989))

Open in new window

0
 
jimtpowersCommented:
Sorry, forgot the visitid.

SELECT *  
FROM   table  
WHERE (lngDataid IN (145, 1989))
  AND (visitid = 1234)

Open in new window

0
Choose an Exciting Career in Cybersecurity

Help prevent cyber-threats and provide solutions to safeguard our global digital economy. Earn your MS in Cybersecurity. WGU’s MSCSIA degree program was designed in collaboration with national intelligence organizations and IT industry leaders.

 
running32Author Commented:
That returns the records if they have a 1989 or 145.  I need it if they have both not just one or the other.

Thanks
0
 
jimtpowersCommented:
Some sample data along with the table structure may be useful.
0
 
8080_DiverCommented:
Try the attached.
SELECT {list of columns}
FROM
(
SELECT {list of columns}
FROM {yourtablename}
WHERE VisitID = @VisitID
  AND lngDataid = 145
) Y
FULL OUTER JOIN
(
SELECT {list of columns}
FROM {yourtablename}
WHERE VisitID = @VisitID
  AND lngdataid = 1989
) Z
WHERE Y.lngDataid IS NOT NULL
  AND Z.lngDataid IS NOT NULL;

Open in new window

0
 
running32Author Commented:
Sample data would be

tblMHCtest

lngvisitid    lngDataid
9999              1989
9999                145
7777               1989
7777               1234
6666               1989
6666                 145
6666               1234

I would want to retrieve visitid 9999 and 6666.

0
 
jimtpowersCommented:
So, the result set you are looking for woud be:

lngvisitid    lngDataid
9999              1989
9999                145
6666               1989
6666                 145
0
 
running32Author Commented:
No I'm just looking for 9999
                                   6666

Thanks
0
 
running32Author Commented:
THANK YOU
0
 
8080_DiverCommented:
Wouldn't the query I posted in response #35201173 provide the desired results if you enter "lngvisitid" as the "list of columns"?

The only other adjustment would be to remove the VistiID from the WHERE clauses, as shown below:
SELECT DISTINCT lngvisitid
FROM
(
SELECT lngvisitid
FROM {yourtablename}
WHERE lngDataid = 145
) Y
FULL OUTER JOIN
(
SELECT lngvisitid
FROM {yourtablename}
WHERE lngdataid = 1989
) Z
WHERE Y.lngDataid IS NOT NULL
  AND Z.lngDataid IS NOT NULL;

Open in new window

0
 
Anthony PerkinsCommented:
It looks like a good solution, but I suspect the author was looking for a more generic solution that would work not only for 2, but also 3, 4, etc.
0
 
running32Author Commented:
Thank you 8080 Diver for your input.   acperkins solutions just suited my needs better.  Sorry I didn't mean to offend you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.