How do I get data from two different tables

[BigDoug]

I need help doing something I think is very complex. I hope I can explain this correctly. I have the output that comes from a perl/MySql call that gives me the "Seller Number" in a straight integer number like 345. That same seller number in a different table is actually the real username of the seller like "TestMan" so Seller # 345 in the Items table = username "TestMan" in the Member table. What I need is an easy way to produce output that takes the seller number and looks at the Members table to translate that seller number to the appropriate username from table Members. This sounds totally confusing to me but that is what I need. Can anyone help to show me which direction I can go to make this happen without writing a new script to accomplish it?

 

[sleipnir214]

What you're doing is a very common operation in relational databases. In fact, the kind of thing you're doing is the entire reason relational database exist.

Suppose tableA has the sellerid you're looking for in a column named "sellerid" and a sale id in a column named "saleid". Also suppose tableB has the detail information on the seller, and that it contains a column "sellerid" (which relates a record to a record in tableA by matching the two sellerids) and a column titled "sellername";

If you want to find the name of a seller for saleid = 3, peform something like:

SELECT sellername from tableA, tableB where tableA.sellerid = tableB.sellerid and tableA.saleid = 3

For more info, point your browser to the MySQL online documentation at

http://www.mysql.com/doc/en

I also can recommend the book MySQL, published by New Riders Press. It has a good primer on SQL with specifics on how MySQL implements it, as well as a good general reference on MySQL.

You might also look online for tutorials on the ins and outs of SQL. Want the best answers? Ask the best questions:

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[BigDoug]

Okay, a little more light is shining but I still have a problem. Here is the call I use now :

SELECT itemnum,category,title,quantity,type,open,close,image1,bold,yellow,grabber,buyitprice,galfeat FROM Items WHERE closef <> 1 ORDER BY close

The problem lies in the fact that I need to also retrieve the username from the table Members along with the other info from this statement. Something like (but this don't work):

SELECT itemnum,category,title,quantity,type,open,close,image1,bold,yellow,grabber,buyitprice,galfeat FROM Items WHERE closef <> 1 AND username FROM Members ORDER BY close

I know I am not explaining this well, and that is probably another part of my problem. I don't exactly what to call and where to accomplish this. Is there a way to call some data from this table and some data from that table when they have different names? In the Items table, the seller is identified by a number (I.E. 345) but in the Members table the seller is identified by a name (I.E. TheBoss). What I need is to get there username from the members table and use that along with the other info I get from the SELECT that grabs records from the Items table. Is it possible in a nutshell? Now I am a little more clear on what I need, but further from understanding how to get it.

 

[sirugo]

OK, you're on the right track.

Your:
SELECT itemnum,category,title,quantity,type,open,close,image1,bold,yellow,grabber,buyitprice,galfeat FROM Items WHERE closef <> 1 AND username FROM Members ORDER BY close

won't work because you are now joining two tables.

Because you are now &quot;joining&quot; two tables you're safer off to specify each field with it's table name, and write it differently.
Your call should be changed to:

SELECT items.itemnum, items.category, items.title, items.quantity, items.type, items.open, items.close, items.image1, items.bold, items.yellow, items.grabber, items.buyitprice, items.galfeat, members.username FROM Items, members WHERE items.closef <> 1 ORDER BY items.close

You don't have to do this if the field name are unique in the tables but I don't know if they are so I chose to add the table-name to each field.

Note that I added &quot;, members&quot; after FROM items because you are also peeking into that second table.

 

[BigDoug]

That worked beautifully when I look at the results through phpMyAdmin. Thank you! The problem now is that the output to the screen just runs and runs and never actually displays any output. I can run the script from the command line and it does the same thing. It seems like it is hanging and hanging now where it didn't before. Any ideas what may be causing this?

 

[swampBoogie]

Well, there is no join condition in the query so you will end up with all possible combination of records from both tables.

I assume that you have some id column in the members tables that corresponds to the sellerid column in the items table.


SELECT items.itemnum, items.category, items.title, items.quantity, items.type, items.open, items.close, items.image1, items.bold, items.yellow, items.grabber, items.buyitprice, items.galfeat, members.username
FROM Items inner join members
on items.sellerid = members.id
WHERE items.closef <> 1 ORDER BY items.close

This will restrict the result to those records where the items.sellerid = members.id match. If this does not help, please show how the tables are defined.

 

[BigDoug]

This is the revised query I am using that has the right table names, but it does not work.

SELECT Items.itemnum,Items.category,Items.title,Items.quantity,Items.type,Items.open,Items.close,Items.image1,Items.bold,Items.yellow,Items.grabber,Items.buyitprice,Items.galfeat,Members.username FROM Items INNER JOIN Members on Items.seller = Members.username WHERE Items.closef <> 1 ORDER BY Items.close

It does not return any results from the database.