CODEWITHSUNDEEP
mysqlsql
w3bguy
w3bguy

Reputation: 2250

MySQL query/subquery issue

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);

Upvotes: 1

Views: 103

Answers (6)

gts
gts

Reputation: 627

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

Upvotes: 1

Nadir Sampaoli
Nadir Sampaoli

Reputation: 5555

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;

Upvotes: 1

Olivier Coilland
Olivier Coilland

Reputation: 3096

Try something like this:

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

Upvotes: 1

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

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

Upvotes: 1

Sebas
Sebas

Reputation: 21532

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.

Upvotes: 1

Marco
Marco

Reputation: 57573

You should use

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

Upvotes: 1

Related Questions