CodingForums.com - Selecting from two tables

CodingForums.com (http://www.codingforums.com/index.php)

- MySQL (http://www.codingforums.com/forumdisplay.php?f=7)

- - Selecting from two tables (http://www.codingforums.com/showthread.php?t=285663)

apotd

01-12-2013 10:25 AM

Selecting from two tables

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.

sunfighter

01-12-2013 05: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.

apotd

01-12-2013 07: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 08: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

Code:

SELECT * FROM dates_and_titles ORDER BY something;

SELECT imageURL FROM images ORDER BY RAND();

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.

apotd

01-12-2013 10: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

Code:

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

So for that

Code:

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 08:16 PM

Two separate queries.

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

Code:



$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.

All times are GMT +1. The time now is 08:09 AM.