Problem query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Problem query

Post by Stryks »

Hi all,

I really dont know why I am having so much trouble with queries of late. I guess I am just asking more of a query now than I once did.

Anyhow, I have two tables which track data on my sites users. There are two types of user, a vendor and a standard user. All login information and basic account info is stored in a table called tbl_user_auth and extended info for vendors is stored in tbl_vendor.

tbl_user_auth has a primary key called user_pk, while tbl_vendor has one called vend_pk and links across to user_pk with a column called user_id

When a user logs on I pull up their record and load a few values to a session variable.

What I am wanting to do is to query the users vendor ID should there happen to be an entry for it in the other table.

The problem I am having is that it works fine if the user has a vendor record, but if there is not one present, it returns zero results.

Code: Select all

SELECT 
  `tbl_user_auth`.`USER_PK`,
  `tbl_vendor`.`VENDOR_PK`
FROM
  `tbl_user_auth`,
  `tbl_vendor`
WHERE
  (`tbl_vendor`.`USER_ID` = `tbl_user_auth`.`USER_PK` )
Is there a way to return values if there is an entry in the second table or not, or should I just call this with a second query?

Thanks for any feedback you can give.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Post by onion2k »

You need a join.

Code: Select all

SELECT
  `tbl_user_auth`.`USER_PK`,
  `tbl_vendor`.`VENDOR_PK`
FROM
  `tbl_user_auth`
LEFT JOIN 
  `tbl_vendor`
ON
  `tbl_vendor`.`USER_ID` = `tbl_user_auth`.`USER_PK`
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Thanks, works like a charm.

Now to figure out why it works :?

Anyhow .. thanks for your time
User avatar
Jean-Yves
Forum Contributor
Posts: 148
Joined: Wed Jul 02, 2003 2:13 pm
Location: West Country, UK

Post by Jean-Yves »

LEFT JOIN means that it will return all records fomr the left hand table, and only the records form the right hand table where the matching criteria exist.

Where there is not a matching record in the right hand side table, null fields are returned for the right hand table, but the left hand record fields are retrieved as per normal

HTH
Post Reply