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 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.
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
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.
SELECT DISTINCT plan.cliorder, plan.*, trkg.curwipprice_online FROM plan LEFT JOIN trkg ON plan.cliorder = trkg.cliorder WHERE plan.cliorder != '' AND plan.enabled != '0000-00-00 00:00:00' AND TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end) ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC, id ASC, ((1-(plan.curwipprice/plan.edprice))*100) DESC
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.
SELECT plan.*, trkg.curwipprice_online, trkg.addr FROM plan LEFT JOIN trkg ON plan.cliorder = trkg.cliorder WHERE (trkg.addr = '5740 OLD CHENEY RD') OR (plan.cliorder != '' AND plan.enabled != '0000-00-00 00:00:00' AND TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end)) ORDER BY ((1-(trkg.curwipprice_online/plan.edprice))*100) DESC, id ASC, ((1-(plan.curwipprice/plan.edprice))*100) DESC
produces this echo in php:
Superboy and His Dog Krypto ----------5740 OLD CHENEY RD
Wonder Woman----------5740 OLD CHENEY RD
Creature from the Black Lagoon ----------5740 OLD CHENEY RD
Seaview 8-Window----------5740 OLD CHENEY RD
Peterbilt 377 A/E ----------1840 Pawnee St
F-86F Sabre----------1840 Pawnee St
Sherman III----------5740 OLD CHENEY RD
Nakajima Ki-84----------5740 OLD CHENEY RD
Panther G----------5740 OLD CHENEY RD
Peterbilt 377 A/E ----------5740 OLD CHENEY RD
F-86F Sabre----------5740 OLD CHENEY RD
Peterbilt 353 ----------1840 Pawnee St
Peterbilt 353 ----------5740 OLD CHENEY RD
How do I get the “1840 Pawnee St” out of the selection from the right table?? I thought the OR would do that.
If you write your query in 1 long line, it gets hard to understand.
Try a format like this:
SELECT
plan.*
, trkg.curwipprice_online
, trkg.addr
FROM plan
LEFT JOIN trkg
ON plan.cliorder = trkg.cliorder
WHERE (trkg.addr = '5740 OLD CHENEY RD')
OR (plan.cliorder != '' AND
plan.enabled != '0000-00-00 00:00:00' AND
TIMESTAMP(NOW()) BETWEEN TIMESTAMP(plan.begin) AND TIMESTAMP(plan.end)
)
ORDER BY
((1-(trkg.curwipprice_online/plan.edprice))*100) DESC
, id ASC
, ((1-(plan.curwipprice/plan.edprice))*100) DESC
Isn’t that easier to read?
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.
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?
SELECT plan.*
, trkg.curwipprice_online
, trkg.addr
FROM plan
LEFT OUTER
JOIN trkg
ON plan.cliorder = trkg.cliorder
WHERE (trkg.addr = '5740 OLD CHENEY RD')
OR ...
to this –
SELECT plan.*
, trkg.curwipprice_online
, trkg.addr
FROM plan
LEFT OUTER
JOIN trkg
ON trkg.cliorder = plan.cliorder
AND trkg.addr = '5740 OLD CHENEY RD'
WHERE ...
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.
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.