View Full Version : Selecting from two tables

01-12-2013, 11:25 AM
Okay so I pretty much know nothing of this, but what I'm trying to do is this:

I want to select all columns from a table called "updates", and one column (named "image") from a table called "portfolio".

I've been trying some things but I don't really have a clue and all I'm getting are errors.

When I look up on how to select information from to tables, I find all things like joining them with inner join and left join etc. But I was wondering what I need to do in this case and if these things are necessary.

01-12-2013, 06:59 PM
I think you need to have something common with both tables in order to do this.

Like "ON TableA.name = TableB.name". With out it I keep coming up with way too much information.

01-12-2013, 08:13 PM
Mh but they have nothing in common, that's not my meaning because I just have this page, on which I want to display updates (just a date and the title of the update, which I have a table for). And on the same page I want to make a div in which random images from another table are displayed.

Do I maybe just have to make a new query for that or something? Because I can't really see how to link those two.

Old Pedant
01-12-2013, 09:49 PM
Well, I know one way to do it, but it would *NOT* guarantee that each record from your primary table would get a *UNIQUE* image from the second table.

In other words, if you showed 10 dates and titles, you might find that you would get 7 different images, with 3 of the images repeated. etc.

And I do see another more complex way to do this where each date/title would get a unique image *provided* there are at least as many image records as there are data/title records.

But, really, it would probably be easier to just do this as two separate queries and let your PHP/ASP/JSP/whatever code get one record from each table.

You could do both

SELECT * FROM dates_and_titles ORDER BY something;

And just run a loop where you get one record from each query each time through the loop.

But if you really need a MySQL solution we could make one.

01-12-2013, 11:43 PM
The easy way that doesn't guarantee unique images is fine. But I'm a bit confused. To get my records from the updates table I use this

$query = "SELECT *FROM updates ORDER BY date DESC";

So for that

SELECT images FROM portfolio ORDER BY RAND();

part, where do I put it? In the same query? Or do I have to make a new query? I'm sorry I'm really completely new to this and when I try putting it together I'm getting errors.

Old Pedant
01-13-2013, 09:16 PM
Two separate queries.

I don't use PHP, but it will be *SOMETHING* like this:

$r1 = mysql_query("SELECT * FROM updates ORDER BY date DESC") or die(mysql_error());
$r2 = mysql_query("SELECT images FROM portfolio ORDER BY RAND()") or die(mysql_error());
while ( $rows1 = mysql_fetch_array($r1) )
$rows2 = mysql_fetch_array($r2) or die(mysql_error());
$image = $rows2["images"];
... now output info for one record from $rows1 ...
... including that image from $rows2 ...

CAUTION: If you don't have at least as many images as you have records in UPDATES table, you will run out of records from $rows2 before you get to the end of $row1.