How to combine results from 2 tables ?

[divinecharm]

Hi !

Here is my problem:

This is for a website for classified Ads. The Ads can be posted by registered users or the Ads can come from external partner sites.

Therefore, I have kept 2 user tables - one for registered users and one for non-registered users, and 1 table for Classified Ads. The Ads table holds Ads from both registered users and external sites, with a column which indicates the source of Ad, i.e. it has two values internal and external.

I want to display all Ads on a page. The user info is to be read from the 2 user tables depending on the source of the Ad. Most of the fields of the 2 user tables are same. However, since we ask registered users to provide more information, the table for registered users has more fields.

Now, how do I go about retrieving data from the 3 tables. Do I need to run two separate queries ? I am a beginner in SQL and look forward to your suggestions.

Thanks,
James

[raf]

You can do it in one query by using a UNION --> http://www.mysql.com/doc/en/UNION.html

But you realy shouldn't have 2 tables for the registered and non-registered user. Just create a table with all the field you need for the two tables (don't use 1 column for 2 different features) and just leave the fields that don't apply blank. Then add an extra column like 'registered' that you set to 1 for registered users.

[divinecharm]

raf, thanks for your suggestion.

Ads from partner sites is a new feature that I am trying to add. The original database design and webpages did not cater for this feature.

If I now use the same table for registered users and non-registered users, I'll have to modify all the pages and include an additional check to make sure that only registered users are able to add product catalogs, list their company etc.

Besides, I planned that once the external user registers on the site, his email will be removed from the non-registered users table and placed in the registered users table. I am not sure if that's the best way to go but I figured that non-registered users table will grow mani-fold as compared to registered user table. And secondly, just adding another table would simplify my work a lot..

Please let me know if my reason justifies 2 tables for users.

Thanks,
James

[raf]

Originally posted by divinecharm
If I now use the same table for registered users and non-registered users, I'll have to modify all the pages and include an additional check to make sure that only registered users are able to add product catalogs, list their company etc.

Even if that is so ... How long would that take you? Probably less then all the time you'll be spending further down the road running the selects against 2 tables and maintaining these 2 tables. I think you need to change your db-design and relevant code. Bad db-design always takes up more time and gets you into more problems so i would set it straight as soon as you can.

If you don't want to set it straight, then use the union.

That you need to run a extra check is a strange argument, and i don't quite see why. When they log in (i suppose they need to identify themselvces somewhere) then you check if they are registered, and redirect them to the 'registered user' zone. And only in this zone, they can work with their accounts data. So i don't see why you would need an extra check since non-registered user wount have acces to the pages to manages the catalogiues or whatever.
Anyway, even if the above would not be dealth with in your application, then all you need to do is set a sessionvariable like
$_SESSION['registereduser'] = 'yes'
and check against that sessionvariable

Originally posted by divinecharm
Besides, I planned that once the external user registers on the site, his email will be removed from the non-registered users table and placed in the registered users table.

If they are storedin one table, then you just need to update the record. In your current design, you can't set up a primary key - foreign key relationship between the usertable and your adds table, because the relatinship is broken if you migrate the user from one table to the other. So you'll never be able to have a real relational design.

Originally posted by divinecharm
I am not sure if that's the best way to go but I figured that non-registered users table will grow mani-fold as compared to registered user table. And secondly, just adding another table would simplify my work a lot..

mySQL has no problem with tables of a few 10k records so unless you have like about a 300 000 users, this is not a valid argument.
Even if you would have 300k records, and they are stored in one table, and you index that 'registered'-field, then mySQL will have no problem to quickly filter out the records you need. Because the index works with references to the 'datapages' that contain registered and non-registered users, and if you run a query with a clause like 'where registered=1', only the datapages with registere users on it will be processed

That it will simplify your work in not true. Not in the long run, for the reasons mentioned above.

Originally posted by divinecharm
Please let me know if my reason justifies 2 tables for users.

No, they don't.