Solved

Sql Select query

Posted on 2011-03-23
13
334 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35200832
SELECT *
FROM   table
WHERE (lngDataid IN (145, 1989))

Open in new window

0
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35200891
Sorry, forgot the visitid.

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

Open in new window

0
 

Author Comment

by:running32
ID: 35200904
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 4

Expert Comment

by:jimtpowers
ID: 35200939
Some sample data along with the table structure may be useful.
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35201173
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
 

Author Comment

by:running32
ID: 35202387
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
 
LVL 4

Expert Comment

by:jimtpowers
ID: 35202691
So, the result set you are looking for woud be:

lngvisitid    lngDataid
9999              1989
9999                145
6666               1989
6666                 145
0
 

Author Comment

by:running32
ID: 35203651
No I'm just looking for 9999
                                   6666

Thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 35203724
Something like this:
SELECT lngvisitid
FROM dbo.YourTable
WHERE lngDataid IN (145, 1989)
GROUP BY lngvisitid
HAVING COUNT(*) = 2

Open in new window

0
 

Author Closing Comment

by:running32
ID: 35208378
THANK YOU
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35208392
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35208959
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
 

Author Comment

by:running32
ID: 35210010
Thank you 8080 Diver for your input.   acperkins solutions just suited my needs better.  Sorry I didn't mean to offend you.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question