difficult query

[bazz]

Can anyone help me understand how to get addresses from tbl_address, where the id for the address is in either tbl_businessDetails or tbl_invoices.

The issues arises, where the person-to-be-invocied, uses an address which may or may not be one of those from which their businesses operate.

~~~~~~~~~~~
tbl_invoices

invoice_id
date_of_invoice
address_id
business_name_id
buyer_id

~~~~~~~~~~~

tbl_address

address_id
name_no
address_1
address_2
address_3

~~~~~~~~~~~

tbl_businessDetails

business_id
business_address_id
contact_id
login_id
business_name
business_type
business_sub_type
business_category
business_type_id


I have tried the following code with conditions switched on and off. Either I get all the addresses or just two - those from tbl_businessDetails.


my $sth = $dbhconnect->prepare ("SELECT BD.business_address_id, ADDR.name_no, ADDR.address_1, ADDR.address_2,
ADDR.address_3, ADDR.town_or_townland, ADDR.city_or_county_name,
ADDR.post_code, ADDR.country_name, LGN.address_id, BD.group_name
FROM tbl_businessDetails BD, tbl_address ADDR, tbl_Login LGN
WHERE BD.business_address_id = ADDR.address_id
AND LGN.address_id = ADDR.address_id
#AND BD.group_name = '$selected_group_name'

") or die "prepare statement failed: $DBO::errstr\n";


Would any of you recommend that I build a table (many-to-many or one-to-many), with the following fields

id | address_id | group_id |

so that I have direct relationship between addresses and the groups that use them.
bazz

[Fumigator]

How about a UNION, which doesn't bring back duplicates (unless you use UNION ALL)?

[bazz]

Fumigator, thank you; I shall look into union.


yay :) finally got it. :thumbsup:


bazz