asked on

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

jimtpowers

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

Open in new window

jimtpowers

Sorry, forgot the visitid.

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

Open in new window

running32

ASKER

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

jimtpowers

Some sample data along with the table structure may be useful.

8080_Diver

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

running32

ASKER

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.

jimtpowers

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

lngvisitid lngDataid
9999 1989
9999 145
6666 1989
6666 145

running32

ASKER

No I'm just looking for 9999
6666

Thanks

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

running32

ASKER

THANK YOU

8080_Diver

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

Anthony Perkins

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.

running32

ASKER

Thank you 8080 Diver for your input. acperkins solutions just suited my needs better. Sorry I didn't mean to offend you.