Solved

Copying/Inserting row from database prefix to another prefix.

Posted on 2008-10-17
13
464 Views
Last Modified: 2012-05-05
Hello Experts,

In my database I have prefix called "prefix_country" which contain field named "country_id" .. I would like to include this field "country_id" in another prefix called "prefix_rating" ..

I'm sure it's easy task but I'm not that good in SQL databases.
0
Comment
Question by:Dido123
  • 7
  • 6
13 Comments
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22747108
Can you provide some information on the tables involved?
0
 

Author Comment

by:Dido123
ID: 22750708
I have Articles website .. In my database there are two tables :

- "prefix_users" which contain authors info like (Country , Name , Email , Signup date , etc..)
- "prefix_articles" which contain articles info like (article id, author userid, post date, words count, etc..)

The "prefix_articles" doesn't contain country id for each article which can be found in the "prefix_users" under "country_id" ... I would like to include the country id for each article.

I hope this is make it more clear. Please let me know if there is anything else.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22750759
I presume prefix_articles is related to prefix_users through the `author userid` field?  I will assume the parent field is called `userid`.  In that case:

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.userid=b.`author userid` WHERE b.`article id`=<article id>
0
 

Author Comment

by:Dido123
ID: 22750772
Thanks .. I will try that now but first, Will I do this one time and from now on all new articles will be submitted to my database will have the country id assigned automatically Or I will have to do this task from time to time to keep it updated?


Thank you so much in advance.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22750822
The country_id field is already present by virtue of the relationship.  There's no need to duplicate the data in the child table (prefix_articles).  The query will supply the proper country_id each and every time.
0
 

Author Comment

by:Dido123
ID: 22758296
After some errors I posted your code that way :

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE b.`article id`=`article id`

the "<" in <article id> seems to be the reason for the errors.

Anyway no errors now but nothing changed.

Please let me know what I do wrong.
This is the code I used.
 
 

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE b.`article id`=`article id`

Open in new window

0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 50

Expert Comment

by:Steve Bink
ID: 22758327
Where <article id> appeared, you need to put the actual article ID you are searching for.  For example:

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE b.`article id`=1
0
 

Author Comment

by:Dido123
ID: 22758355
I think you misunderstood me. I would like to add country ID to each article in my database.

Let me know If you need to know anything else from me to give the accurate solution.


Thank you for your time.
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 22758612
That's what I tried to explain earlier: the information already exists.  There is no need to duplicate the information since you have access to it through the relationship between your two tables.  Every time you query for a list of articles, using this JOIN will allow you to have the country_id included without requiring the additional space of duplicate storage.
0
 

Author Comment

by:Dido123
ID: 22758743
But this query will give me the ability to check only 1 article. I would like to have country ID assigned to all articles so I can search articles by country ID.

Let's say I would like to search/list all articles from USA .. what should I do?


Thanks again for your help.
0
 
LVL 50

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 22758878
In that case, just replace the WHERE clause with the requirements of your search.  If USA is country_id=1, then you would search like this:

SELECT a.country_id, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE a.country_id=1

Your query is not limited to just the country_id, either.  You can include any other fields from prefix_users that you might need, or would like to use as part of a filter.  If you wanted to search based on an author's name:

SELECT a.country_id, a.author_name, b.* FROM prefix_users a INNER JOIN prefix_articles b ON a.user_id=b.`author_id` WHERE a.author_name='Mi Nam'

0
 

Author Comment

by:Dido123
ID: 22765251
You are really great person. It worked! :)


Thank you so much for your time. I wish I could reward you more than 500 points :)


BTW, I have another "open" question about SQL databases. I'm sure you can help on it.

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/Q_23827239.html


Thanks again. Question has been answered.
0
 

Author Closing Comment

by:Dido123
ID: 31507183
Thanks again for your time.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article describes some very basic things about SQL Server filegroups.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now