Member Avatar for jj.amonit

I have 3 tables, all linked. Table 1, Table 2 and Table 3. 1 to many. Table 1 will always have records in Table 2. However, Table 2 does not have to have any records in Table 3.

I want to get the id's and names of the records in Table 2 for a record in Table 1 only if they have records in Table 3.

here's my query

SELECT table2.index , table2.name
FROM table2 , table3
WHERE table2.fk_table1_index` = '126'
AND table2.index = 'table3.fk_table2_id'
GROUP BY table2.index

Now...this returns no records in my php code or phpMyadmin, even though, it worked when I was testing it out in phpMyadmin.

I only want 1 result for each record in Table 2. For example, if a record in Table 2 has 3 records in Table 3, I only want the id and name to show up once in the result. That's why I have the GROUP BY part.
This seems like a simple problem, but I have no idea how to solve it.

Any ideas? Thanks in advance.

  • 2 Contributors
  • 1 Reply
  • 78 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by apegram
Member Avatar for apegram

You're selecting two non-aggregated fields and only grouping on one. For the query to execute properly, you need to group on each field that is not an aggregate or a function. But as for this example, consider dropping the Group By clause in favor of Select Distinct.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.