getting values in a table SQL

IssacJones
IssacJones used Ask the Experts™
on
Hi

I have two tables A and B:

table A
======

ID_A     NAME      RESULT

table B
======

ID_B    FK_A    ADDRESS

As you can see, table B has a link to A via the primary key ID_A i.e. a one to many relationship.

What I would like to do is to make a query that returns the rows in A that have a link to the table in B (but only once).

For example, suppose that A has values

1    Eric   7.2
2    Mark  8.3
3    Joshua 9.2
4    Mary    3.2

Suppose that B has values of FK_A equal to 1 and 3 but not 2 and 4.

I would like to return in the query

1  Eric   7.2
3    Joshua 9.2

irrespective of the number of times FK_1 and FK_3 occur in table B i.e. the query only returns two records.

Can anybody help?

Issac
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Kent DyerIT Security Analyst Senior

Commented:
SELECT DISTINCT or..

SELECT TOP 1

Does that help?

Thanks,

Kent

Commented:
Try this:

SELECT DISTINCT * FROM A WHERE ID_A IN (SELECT FK_A FROM B);
Commented:
Or better yet, a classic left join (http://www.w3schools.com/sql/sql_join_left.asp).

SELECT DISTINCT *
FROM A
LEFT JOIN B
ON A.ID_A=B.FK_A

This is the preferred way of doing it. The previous suggestion is useful in more complex queries when a left join cannot be used.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial