sub-query assistance please

[bazz]

Hi,

I am querying two tables based on set criteria. If there is a match, I think I need to do a sub query to get the full details.

I need to get all the matched field values for address from tbl_address if there is a match.



my $sth = $dbh->prepare ("SELECT BD.business_id, BD.business_address_id, BD.group_name, ADDR.address_id, ADDR.city_or_county_name
FROM tbl_businessDetails BD, tbl_address ADDR
WHERE BD.group_name = '$selected_group_name'
AND BD.business_address_id = ADDR.address_id
AND BD.premises_name = '$selected_premises_name'
AND BD.business_name = '$selected_business_name'
") or die "prepare statement failed: $DBO::errstr\n";



What I need to do is: if there is a match, I need to call in the data from the tbl_address. Please can you give me a pointer.

bazz

[oesxyl]

the query return only the line from both tables that match the criteria:


BD.group_name = '$selected_group_name'
AND BD.business_address_id = ADDR.address_id
AND BD.premises_name = '$selected_premises_name'
AND BD.business_name = '$selected_business_name'


but in this case BD.bussiness_address_id and ADDR.address_id have same value you don't need both

also you need full detali from table with alias ADDR so you can get all row what match the criteria, ADDR.*

that means your query become:


SELECT BD.business_id, BD.group_name, ADDR.*
FROM tbl_businessDetails BD, tbl_address ADDR
WHERE BD.group_name = '$selected_group_name'
AND BD.business_address_id = ADDR.address_id
AND BD.premises_name = '$selected_premises_name'
AND BD.business_name = '$selected_business_name'


you allready did it, :)

best regards

[bazz]

heh, its obviously time for bed (at 0144) so I am thinking too hard :D

thanks oesxyl :thumbsup: