'MySQL subquery overview

Alright I am guessing I need a subquery to solve this and I am little rusty on these. So I have 3 tables:

tblAccount - Has User information and AccountID
tblItem - Has Item information and ItemID
tblAccountItem - Has 3 fields - AccountItemID / AccountID / ItemID

An account can have many items and an item can have many accounts. Example data:

tblAccount

AccountID    AccountName     AccountEmail
1            John Smith      [email protected]
2            Fred John       [email protected]
3            George Mike     [email protected]

tblItem

ItemID       ItemName        ItemDescription
1            Hammer          Smashes things
2            Axe             Breaks things

Ok so lets say the Hammer belongs to John,Fred and George. Axe only belongs to John and Fred.

tblAccountItem

AccountItemID       AccountID     ItemID
1                   1             1
2                   2             1
3                   3             1
4                   1             2
5                   2             2

So I want to show what items John has and also show who else owns that item. The output I want to show is:

ItemName            ItemDescription    OtherOwners
Hammer              Smashes things     Fred, George
Axe                 Breaks things      Fred

Any help would be greatly appreciated!

The answer by ctrahey is perfect but I have a slight condition to add. There are 2 types of accounts in tblAccount denoted by a field.

tblAccount

AccountID    AccountName     AccountEmail       AccountDescription    AccountTypeID
1            John Smith      [email protected]     NULL                  1
2            Fred John       [email protected]      NULL                  1
3            George Mike     [email protected]    Runner                2

tblAccountTypeID

AccountTypeID   AccountType     
1               User      
2               Admin

If the AccountTypeID is 1 then I need to output the AccountEmail. If the AccountTypeID is 2 I need to output the AccountDescription. Eg output (same story as above):

ItemName            ItemDescription    OtherOwners
Hammer              Smashes things     Fred, Runner
Axe                 Breaks things      Fred

Going off the query that ctrahey I am guessing there needs to be an ALIAS field created. Something like:

WHERE AccountTypeID = 1 (SELECT AccountName)
WHERE AccountTypeID = 2 (SELECT AccountDescription)

I hope this makes sense, thanks for all the help so far!

mysqlsubquery


Solution 1:[1]

Subqueries are very rarely actually needed, and are often replaced (with improved performance) by a well-designed JOIN.

Here, we start with AccountItem table (the WHERE clause immediately limits the query to only items owned by our account of interest); then we join the same table (aliasing it to 'others_items_join'), telling it to join with the same itemID but not owned by our account if interest. That's the essence of the entire query, the next two joins are only to bring in the actual strings we want to be in our output (the other people's names and the item names/descriptions). GROUP BY is used to give just one row per item which our account of interest has.

SELECT 
  ItemName,
  ItemDescription,
  GROUP_CONCAT(others.AccountName) as OtherOwners
FROM 
  tblAccountItem as my_items
  LEFT JOIN tblAccountItem as others_items_join 
    ON others_items_join.ItemID = my_items.ItemID AND others_items_join.AccountID != ?
  LEFT JOIN tblAccount as others
    ON others_items_join.AccountID = others.AccountID
  JOIN tblItems ON my_items.ItemID = tblItems.ItemID
WHERE my_items.AccountID = ?
GROUP BY ItemName

Solution 2:[2]

You better use a coding to resole this , here rough query that's may helps you get an idea :

SELECT AccountName
FROM tblAccount
WHERE AccountID = (SELECT AccoundID 
                   FROM tblAccountItem
                   WHERE itemID = (SELECT itemID
                                   FROM tblAccountItem
                                   WHERE AccountID = 1 (john Id as example)));

Hope this helps

Solution 3:[3]

SELECT ItemName, ItemDescription, AccountItemID FROM tblitem RIGHT JOIN tblaccountitem ON tblitem.ItemID=tblaccountitem.ItemID

I hope this helps lead to your answer. I am still looking into this

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Chris Trahey
Solution 2 RedhopIT
Solution 3 Henrey Bellchester

Related Questions