Query: All of A but not all of B

[John Zelmer]

Hello,

I have table A which is linked to table B via an additional link table AB. I'm trying to make a query which shows all table A's records but only certain table B's records which meets criteria that are set in the query in table AB's fields.

If the criteria are not met, I get no table A record.

The link setting in the query is correct: Show all of table A and only those that match of table AB.

Any suggestions how to go about this?

Thanks in advance,
John

 

[spikepl]

This is a lot of words to grasp something which is much better illustrated by tables and queries - show screenshots of table relations and queries and SQL or attach a db.

Oh yeah - what is the actual issue/question? That is not clear from the narrative.

 

[John Zelmer]

Sorry, I'll try to explain with a simplified example.

Table Person
IDperson*
Name

Table PersonAddress
IDperson*
IDaddress*
Current (Yes/No)

Table Address
IDadress*
Street
City

I want to make a selection query that gives me all persons (period) and.... the relevant adresses (that is: where the field Current is Yes).

In the query the tables are linked like this.
Person-->PersonAddress-->Address

If I set the aforementioned condition (Current is Yes), the query won't give me the persons anymore that have no adresses. If I leave out the condition it does give me all the persons, even the ones without an address.

The condition (filter) seems to override my 'link setting' between table Person and table PersonAddress, which is: All records of Person and only those of PersonAddress for which the linked fields are identical.

I have kinda solved it, by making an additional query, but I was wondering if this could be done in just one query?

Regards,
John

 

[nfk]

SELECT a.PINID, 
       b.Address, 
       c.FirstName, 
       c.LastName
FROM   a LEFT OUTER JOIN
       c ON a.ID = c.ID LEFT OUTER JOIN
       b ON a.AddressID = b.AddressID
WHERE  b.Current = 1

 

[John Zelmer]

Thanks but not sure I follow.

The field Current is in the relation table and not the address table.
b.Current doesn't seem right then. I tried the two LEFT OUTER JOIN's but then I get no records at all.

John

 

[nfk]

Thanks but not sure I follow.

The field Current is in the relation table and not the address table.
b.Current doesn't seem right then. I tried the two LEFT OUTER JOIN's but then I get no records at all.

John

What about excluding on a subquery, this query will exclude all dead people before joining and showing their addresses.

SELECT        dbo.tblAddresses.AddressID, dbo.tblAddresses.PINID, d.PINID 
FROM            dbo.tblAddresses RIGHT OUTER JOIN
                             (SELECT        PINID
                               FROM            dbo.tblPeople
                               WHERE        (blnDeceased = 1)) AS d ON dbo.tblAddresses.PINID = d.PINID

 

[gemma-the-husky]

possibly this
"true" is -1, not plus 1

 SELECT a.PINID, 
       b.Address, 
       c.FirstName, 
       c.LastName
FROM   a LEFT OUTER JOIN
       c ON a.ID = c.ID LEFT OUTER JOIN
       b ON a.AddressID = b.AddressID
WHERE  b.Current = [COLOR=red]-1[/COLOR]

note that using a Boolean in an outer join may fail because of the issue described below. Not sure if this is exactly your scenario, but it may be - you said you got no records. If it is the query actually fails to run with an error "no current record"

http://allenbrowne.com/bug-14.html

 

[nfk]

possibly this
"true" is -1, not plus 1

 SELECT a.PINID, 
       b.Address, 
       c.FirstName, 
       c.LastName
FROM   a LEFT OUTER JOIN
       c ON a.ID = c.ID LEFT OUTER JOIN
       b ON a.AddressID = b.AddressID
WHERE  b.Current = [COLOR=red]-1[/COLOR]

note that using a Boolean in an outer join may fail because of the issue described below. Not sure if this is exactly your scenario, but it may be - you said you got no records. If it is the query actually fails to run with an error "no current record"

http://allenbrowne.com/bug-14.html

Yeah, I'm using SQL Server 2005...

 

[John Zelmer]

Thanks Gemma, NFK,

I was aware of the Boolean value 1 in stead of -1. But that has no effect on solving the problem. Seems there's no other option than to use the extra query.

John

 

[vbaInet]

I have kinda solved it, by making an additional query, but I was wondering if this could be done in just one query?

The subquery in post #6 by nfk should do it but in Access you're better off with multiple queries in most cases. Just ensure that you filter down the data in the first query, before joining it.

 

[gemma-the-husky]

Thanks Gemma, NFK,

I was aware of the Boolean value 1 in stead of -1. But that has no effect on solving the problem. Seems there's no other option than to use the extra query.

John

are you getting "no current record"?

if so, it is caused by the bug described in the AllenBrowne article. The fix is to cast the Boolean to a numeric, and then test for non-null in the numeric, rather than the boolean

 

[nfk]

I just build your example (if I actually getting it right) and your right, it doesnt work with "= 1" but it does with "=True" is it that?

 

[John Zelmer]

Sorry for late reaction. Although the True value works, your query doesn't give the right result. From my earlier post:

"I want to make a selection query that gives me all persons (period) and.... the relevant addresses (that is: where the field Current is Yes)."

With your query I don't get Clare, which I do want to get, with no address data.

John