In a single query, join a row within a table based on a column with unique stock number, with a targeted row within within a group of rows with the same stock number in another table?

If so how do you target the non unique row?

cpradio
—
2012-10-05T15:32:06Z —
#2

Do you mean performing a JOIN?

SELECT columns_from_first_table, columns_from_second_table
FROM first_table AS t
LEFT JOIN second_table AS t2 ON t.stock_number = t2.stock_number

guido2004
—
2012-10-05T15:42:21Z —
#3

If you want a specific row from the group of rows with the same stock number in the other table, you'll have to add ad a WHERE clause that specifies the identifying column-value pair for that specific row.

nichemtktg
—
2012-10-05T17:10:22Z —
#4

Thanks cpradio and guido2004.

Now, is it possible to select qualified rows from table1, based on WHERE and add a column from table2 when a column from table1 matches a column from table2?

cpradio
—
2012-10-05T17:15:50Z —
#5

nichemtktg said:

Thanks cpradio and guido2004.

Now, is it possible to select qualified rows from table1, based on WHERE and add a column from table2 when a column from table1 matches a column from table2?

Yes, that would be using a JOIN with a WHERE clause

SELECT columns_from_first_table, columns_from_second_table
FROM first_table AS t
LEFT JOIN second_table AS t2 ON t.stock_number = t2.stock_number
WHERE t.column_from_first_table = X

nichemtktg
—
2012-10-05T20:52:39Z —
#6

Thanks cpradio.

I've written this query and have analyzed the results and am satisfied that it does what I need it to do. Thanks again to cpradio and guido2004.

The DISTINCT will not do what you think it does. It will act on all columns in your query. You asked for one value from a group of stock numbers. The DISTINCT will return all rows from that GROUP of rows if any columns differ. So if you had five rows in groupid=24 for example, all of them will be returned if they satisfy the WHERE clause.

nichemtktg
—
2012-10-05T23:52:53Z —
#8

Then what keyword should I use to return one row for each unique stock number (we call them cliorder)?

With the OR, your query takes all rows that have OR the '5740' address, OR all the conditions in the part after the OR. One of them is enough. I don't know what the criteria are for the rows you want to extract, so I can't tell you how to write the WHERE conditions.

nichemtktg
—
2012-10-06T15:43:54Z —
#11

Thanks again guido2004.

Based on my previous code, I changed the WHERE to:

(TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) AND trkg.addr = '5740 OLD CHENEY RD')

this produces ten matches I can use, but fails to select rows from the LEFT table that DON'T have a corresponding match in the RIGHT table (makes sense). How do modify this query to also SELECT rows from the LEFT table that don't match the RIGHT table?

Removing the '5740 OLD CHENEY RD' requirement in the WHERE selects those unmatched rows plus all the non '5740 OLD CHENEY RD' addresses that I don't want - catch-22! That makes me think I need an OR in my WHERE to select those unmatched rows from the LEFT, but so far no joy. Can an OR be wriiten to do that or do I need to change my approach?

r937
—
2012-10-06T15:56:44Z —
#12

[quote="nichemtktg,post:11,topic:21955"]How do modify this query to also SELECT rows from the LEFT table that don't match the RIGHT table?[/quote]change this --

I'm sure you hear this a lot r937, very cool! Thank-you. I still have a little hair left.

The google didn't take me to documentation for your use of AND in a LEFT OUTER JOIN. Please post a link if you have one.

guelphdad
—
2012-10-06T17:18:43Z —
#14

You use AND in the LEFT JOIN when you have conditions that must be matched in the right hand or second table. If you put those conditions in a where clause, those are applied after the join and the NULL or unmatched rows would be eliminated from the results. Essentially having them in the WHERE changes the LEFT JOIN to an INNER JOIN.

nichemtktg
—
2012-10-06T18:15:47Z —
#15

Thanks guelphdad.

Was there some way I could've learned that in the manual?

The longer I code the more I go to the manual and google first before posting, but I just couldn't find anything before posting. I'm sure that's because I didn't use an effective query.

What's this situation even called? If you where to google it, what keywords would you use?

The conditional_expr used with ON is any conditional expression of the form that can be used in a WHERE clause. Generally, you should use the ON clause for conditions that specify how to join tables, and the WHERE clause to restrict which rows you want in the result set.

.. is from the manual and I believe it covers this situation. I read that page before, but obviously didn't make the connection. Thankfully, you all are available.