Join unique row in one table with a non unique row in another

Is it possible to do this:

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?

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

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.

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

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.

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.

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

Thanks guelphdad.

This code:

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? :slight_smile:

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.

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?

change this –

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

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.

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.

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 following MySQL page has a bit more information about JOINs on it and shows an example of using AND and OR within a JOIN
http://dev.mysql.com/doc/refman/5.0/en/join.html

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.

I think this topic has concluded.

not too often, but enough to make me keep doing it

:smiley: