Page 1 of 1

Merging two queries - Can this be done?

Posted: Mon Dec 13, 2004 6:50 am
by Stryks
Hi all. 8)

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')
Then, for each one of these I want to return the phone numbers stored in their address book.

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`
Can this be done, or am I trying to do something that is impossible or illogical?

Any help here would be great. Thanks for reading. :)

Posted: Mon Dec 13, 2004 8:46 am
by CoderGoblin
Should be feasable..

Code: Select all

SELECT DISTINCT * FROM tbl_vendor,tbl_user_auth,tbl_user_address WHERE tbl_vendor.USER_ID=tbl_user_auth.USER_PK AND tbl_user_address.USER_ID = tbl_user_auth.USER_PK AND tbl_vendor.status = 1 GROUP BY user_id ORDER BY tbl_user_address.UA_PK;
OK I've got all the columns and remove the quotes which you may need, you would need to sort out the precise columns required and add quotes again, but try first as is. Mixing case in databases can cause problems with readability (In postgres you then need the quotes). I tend to always have table names and column names as lowercase. In my select statements I then have SQL words in Uppercase. You will also notice I miss out the 'INNER JOIN' information as this gets defaulted by all databases I know about and makes the select more readable.

Hope this helps/works.

Posted: Mon Dec 13, 2004 8:58 pm
by Stryks
Hi,

Thanks for the reply. For the most part it works a charm, and I have re-added the columns I need and it all seems to work OK apart from one thing.

Basically, the address book allows the user to store more than one set of contact details for themselves by saving multiple records with the same USER_ID. What I was hoping to do was to retrieve all the different phone numbers for each person with this one query, however it only seems to return their first one.

Is there any possible workaround for this besides removing the grouping and manually checking for duplicates?

Thanks again.

Posted: Mon Dec 13, 2004 10:32 pm
by John Cartwright
search through the mysql manual for DISTINCT, it will remove duplicates.