MySQL query/subquery issue

Question

I am building a query to gather data from multiple tables. I want to grab all of the item_ids from one table and then build the results by gathering other data from other tables based on that item_id. I could have sworn that this was possible; but, I can't seem to get it to work. Here is an example of what I'm trying to do:

SELECT item_id AS mainID, 
 (SELECT anotherField 
  FROM anotherTABLE 
  WHERE item_id=mainID) 
FROM theMainTable;

Granted that is just an example. Essentially, I need to use the item_id from the main query inside the subquery. I could have sworn that I'd done this before, but I can't remember how...

I'm trying to do this with just a query, not with any extra programming languages. I'd like to eventually set it as a stored procedure. Thanks or any assistance with this.

UPDATE

Looks like a join did work... Thanks for all the assistance.

Here is my final query just in case anyone else runs into something like this:

SELECT DISTINCT
    Q.item_id, Q.timestamp, Q.employee,
    QA.employeeNotes, Q2.itemDesc, Q2.itemExtraData
FROM 
    itemLog Q 
LEFT JOIN 
    itemComments QA ON Q.item_id = QA.item_id 
LEFT JOIN 
    itemLog Q2 ON Q.item_id = Q2.item_id AND Q2.type = 'begin' 
WHERE 
    Q.verb = 'itemStatus' 
    AND NOT (QA.employeeNotes IS NULL);

I didn't try a join, because I figured the actual query was going to be too much for that. I'll give that a shot... — w3bguy
– w3bguy, Commented Jun 22, 2012 at 14:43
you should use an inner join with QA, since you want one of its fields not null in the where clause - Thanks for accepting my answer. — Sebas
– Sebas, Commented Jun 22, 2012 at 15:22

Sebas · Accepted Answer · 2012-06-22 14:33:21Z

1

SELECT a.item_id AS mainID, b.anotherField 
FROM theMainTable a
    INNER JOIN anotherTABLE b ON a.item_id=b.item_id

You should avoid using subqueries in select statements, because they will be computed for each returned row from the main table, whereas the inner join ensures proper optimization and tables paths.

answered Jun 22, 2012 at 14:33

Sebas

21.6k9 gold badges60 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marco · Accepted Answer · 2012-06-22 14:32:59Z

1

You should use

SELECT mt.itemID AS mainID, at.anotherField
FROM theMainTable mt INNER JOIN anotherTABLE at
ON mt.itemID = at.item_id

answered Jun 22, 2012 at 14:32

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Comments

Romil Kumar Jain · Accepted Answer · 2012-06-22 14:33:41Z

1

SELECT themaintable.item_id AS mainID, 
       anothertable.anotherfield 
FROM   themaintable 
       INNER JOIN anothertable 
               ON item_id = mainid;

answered Jun 22, 2012 at 14:33

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

Comments

Olivier Coilland · Accepted Answer · 2012-06-22 14:33:45Z

1

Try something like this:

SELECT
    item_id AS mainID,
    anotherField
FROM theMainTable
INNER JOIN anotherTABLE ON item_id=mainID;

answered Jun 22, 2012 at 14:33

Olivier Coilland

3,11618 silver badges20 bronze badges

Comments

Nadir Sampaoli · Accepted Answer · 2012-06-22 14:36:45Z

1

Join the two tables, that's why you have keys:

SELECT table_x.id, table_y.another_field
FROM table_x
INNER JOIN table_y ON table_x.id = table2.x_id;

answered Jun 22, 2012 at 14:36

Nadir Sampaoli

5,5654 gold badges24 silver badges34 bronze badges

Comments

gts · Accepted Answer · 2012-06-22 14:43:47Z

1

If you really need to have a nested query then do:

SELECT item_id AS mainID, 
   (SELECT anotherField 
    FROM anotherTABLE 
    WHERE anotherTABLE.item_id=theMainTable.item_id) AS anotherField
FROM theMainTable;

The reason the nested query was not working could be because you were not defining the table the field was coming from and there was column name ambiguity.

Having said the above, this really is a case where you would use a join instead:

SELECT theMainTable.mainID, 
       anotherTABLE.anotherField
FROM theMainTable INNER JOIN anotherTABLE
ON theMainTable.item_id = anotherTABLE.mainID

answered Jun 22, 2012 at 14:43

gts

6275 silver badges13 bronze badges

Collectives™ on Stack Overflow

MySQL query/subquery issue

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related