Two tables how to handle this join?

sgtpepper

Hello,

I'm stuck on how to handle the following query on two tables that have no common relationship. Here is what I am trying to accomplish.

TABLE 1

This table currently has two columns (although it may have more in the future). One column is called INTRO the other is called OUTRO. The table is filled with phrases that represent the intro an outro of a sentence. For example some fields in the INTRO column could be

1) What a great day
2) Today is a beautiful day
3) On day's like this
4) It's a beautiful day in the neighborhood

The column for OUTRO could have fields like

1) thanks for stopping by.
2) and that is all I have to say about that.
3) until next time.
4) see you tomorrow.

Now, table2 is called 'details'. This table has several columns (more then table1). The columns represent details on vehicles. For example columns like: MAKE, MODEL, YEAR, COLOR, etc.

So, I want to create a query that will randomly select an INTRO from table1 and randomly select an OUTRO from table1 and include it in the results from table two. So, the results would be something like this.

[INTRO.TABLE1] [YEAR.TABLE2] [MAKE.TABLE2] [MODEL.TABLE2] [OUTRO.TABLE1]

I am getting close....however any JOIN I attempt does not randomize the results of table1. What happens is that whatever INTRO is displayed.....the OUTRO is always the outro from the result row.

For example lets say the table looks like this:

INTRO | Outro

Today is good | thats is the end
Today is fair | thanks for the intro.
Intro test 1 | outro test 1

So, when I expect this:

[INTRO.TABLE1] [YEAR.TABLE2] [MAKE.TABLE2] [MODEL.TABLE2] [OUTRO.TABLE1]

I actually get this:

Today is good [YEAR.TABLE2] [MAKE.TABLE2] [MODEL.TABLE2] thats is the end
Today is fair [YEAR.TABLE2] [MAKE.TABLE2] [MODEL.TABLE2] thanks for the intro.

What I really want is RANDOM intro and outros

Today is fair [YEAR.TABLE2] [MAKE.TABLE2] [MODEL.TABLE2] thats is the end
Intro test 1 [YEAR.TABLE2] [MAKE.TABLE2] [MODEL.TABLE2] thanks for the intro.

Remember, TABLE1 and TABLE2 do NOT have the same number of columns. Any idea on how to accomplish what I am looking to do? I hope I explained it without being too confusing.

Thanks

bradgrafelman

Sounds like you need to join the first table twice; once to get a random intro and again to get a random outro.

And since we're talking about databases and random results.. don't forget: Fast Random Results - "ORDER BY RAND()" is bad

sgtpepper

How do I write the JOIN when there is nothing else related to the two tables. I should add that both tables do have an Id column.

sgtpepper

OK. Not sure how to proceed but I do have a LEFT JOIN that is getting closer to what I am looking for. It fills some of the rows in as NULL. Is there a way to have the NULL results randomly filled?

select inventory.Id, inventory.make, inventory.stock, fills.opening_intro,fills.opening_close from inventory LEFT JOIN fills on inventory.ID=fills.Id ORDER BY inventory.stock

Id make stock opening_intro opening_close

51 Toyota 24741A NULL NULL
64 Toyota 24751 NULL NULL
65 Toyota 24755 NULL NULL
55 Toyota 24767 NULL NULL
41 Toyota 24775A NULL NULL
2 Toyota 24779 Check out this that we have on the lot.
9 Toyota 24780 NULL NULL
66 Toyota 24781 NULL NULL
67 Toyota 24790 NULL NULL
7 Toyota 24792 NULL NULL
8 Toyota 24793 NULL NULL
1 Toyota 24794 This is a beautiful. it wont stay on the Fenton lot long.
6 Toyota 24802 NULL NULL
40 Toyota 24807A NULL NULL

dalecosp

Does INNER JOIN give similar results but without NULLs?

sgtpepper

The INNER JOIN removes the NULL results however I only get 5 total results (right now there are only 5 entries in the FILLS database) I need all of the 69 entries from the INVENTORY table to show as results.

dalecosp

Is this a PHP script? 'Cause you could reduce the complexity, possibly, by reading the "intros" and "outros" into a memory structure and then using PHP to do the concatenation...

Given that we're in the Database forum, it's possibly you're not programming, I guess....

barand

Use CROSS JOINS. This will give you all possible combinations from which you can pick at random

SELECT a.intro, c.year, c.make, c.model, b.outro
FROM table1 a
    CROSS JOIN table1 b
    CROSS JOIN table2 c

johanafm

barand;11041613 wrote:
Use CROSS JOINS. This will give you all possible combinations from which you can pick at random

But just like order by RAND() isn't the most efficient way of doing things. As stated in the previous post "cross join" really is "all possible combinations" of the joined sets. With 1K rows in each table, and two tables, the cross join will contain 1M rows. With three tables and 1K rows each, the cross join is 1G rows. Unless you have really small data sets in each table, issuing several queries will be a lot more efficient. Assuming you have a consecutive numbering of your intro and outro rows (otherwise achieving this would be step one)
1. select max(id) from outro
2. generate a random number between 1 and max from outro, @rand_out
3. select max(id) from intro
4. generate a random number between 1 and max from intro, @ramd_in
5. select only field values corresponding to the rows containing those numbers

SELECT i.intro, o.outro
FROM intro i, outro o
WHERE i.id = @rand_in AND o.id = @rand_out

A comma listing of tables is the same as doing a cross join. But as soon as you select just one row from each table, the cross join contains only one row with those values.