I've got a situation where I want to call a stack of information from the database and I'd like to have all information brought back in a single query. The problem is that my data is spread across 3 different tables. I can achieve what I want in a roundabout way using two individual queries, but as I say, I dont know why this shouldn't be possible with one.
The easiest way will probrably just to post the two SQL statements.
Firstly, I want to get a list of all users who are vendors and have a certain status.
Code: Select all
SELECT
`tbl_vendor`.`VENDOR_PK`,
`tbl_vendor`.`USER_ID`,
`tbl_user_auth`.`GivenName`,
`tbl_user_auth`.`Surname`,
`tbl_user_auth`.`Email`,
`tbl_vendor`.`busName`,
`tbl_vendor`.`tradeName`,
`tbl_vendor`.`ABN`,
`tbl_vendor`.`address`,
`tbl_vendor`.`suburb`,
`tbl_vendor`.`state`,
`tbl_vendor`.`postcode`,
`tbl_vendor`.`phone`
FROM
`tbl_vendor`
INNER JOIN `tbl_user_auth` ON (`tbl_vendor`.`USER_ID` = `tbl_user_auth`.`USER_PK`)
WHERE
(`tbl_vendor`.`status` = '1')Code: Select all
SELECT
`tbl_user_address`.`Title`,
`tbl_user_address`.`Phone`
FROM
`tbl_user_address`
INNER JOIN `tbl_user_auth` ON (`tbl_user_address`.`USER_ID` = `tbl_user_auth`.`USER_PK`)
WHERE
(`tbl_user_auth`.`USER_PK` = '45')
ORDER BY
`tbl_user_address`.`UA_PK`Any help here would be great. Thanks for reading.