Solved

Sql Select query

Posted on 2011-03-23
13
329 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
  • 5
  • 4
  • 2
  • +1
13 Comments
 
LVL 4

Expert Comment

by:jimtpowers
Comment Utility
SELECT *
FROM   table
WHERE (lngDataid IN (145, 1989))

Open in new window

0
 
LVL 4

Expert Comment

by:jimtpowers
Comment Utility
Sorry, forgot the visitid.

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

Open in new window

0
 

Author Comment

by:running32
Comment Utility
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
 
LVL 4

Expert Comment

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

Expert Comment

by:8080_Diver
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Expert Comment

by:jimtpowers
Comment Utility
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
Comment Utility
No I'm just looking for 9999
                                   6666

Thanks
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
Comment Utility
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
Comment Utility
THANK YOU
0
 
LVL 22

Expert Comment

by:8080_Diver
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

744 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now