Solved

Sql Select query

Posted on 2011-03-23
13
335 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

705 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