CODEWITHSUNDEEP
mysqlsqldatabase
user2578185
user2578185

Reputation: 437

Select all IDs that have that value in MySQL

I have these two tables:

Table 1:

ID           ITEM
--------------------
1             AB
1            22S
1             AB
2            F45
2             BB
3             1
3             1
3             AA
3            F45
3            F67
3             A
......

Table 2:

ITEM   COUNTRY
---------------
0       usa
1      italy
2      mexico
3      mexico
A      greece
AA     malta
AB      usa
AC    pakistan
B       uk 
BB    france
BA    france
BB    russia
F45     uk

And I use the following query to get the Items that are like "A%" for each ID:

SELECT GROUP_CONCAT(ITEM) 
FROM (SELECT Table1.ID, Table1.ITEM, Table1.date 
      FROM Table2 Table2 INNER JOIN Table1 Table1
      ON (Table2.ITEM = Table1.ITEM) WHERE table2.ITEM like "A%" 
      ORDER BY Table1.id, Table1.date, Table2.ITEM) as temp
GROUP BY ID
ORDER BY ID

I want to find a way to edit my query so I can get the above only for the IDs that happen to have item "F45" in them (but I don't want to use this item, just select the IDs that have it).. Any help will be appreciated

Upvotes: 0

Views: 229

Answers (1)

Alma Do
Alma Do

Reputation: 37365

I guess

SELECT GROUP_CONCAT(ITEM) AS group_item
FROM (SELECT Table1.ID, Table1.ITEM, Table1.date 
      FROM Table2 Table2 INNER JOIN Table1 Table1
      ON (Table2.ITEM = Table1.ITEM) WHERE table2.ITEM like "A%" 
      ORDER BY Table1.id, Table1.date, Table2.ITEM) as temp
GROUP BY ID
HAVING group_item LIKE '%F45%'
ORDER BY ID

Or adjust the subquery to select only needed rows (here I'm not sure what are you trying to achieve)

Upvotes: 1

Related Questions