[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Sql Select query

Posted on 2011-03-23
13
Medium Priority
?
338 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

649 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