Member Avatar for ffej2ffej

My scripts are doing a lookup of transactions in table B. Each transaction contains an integer which is an ID of the business for each transaction. I also want the name (a varchar) of the business in the original transaction. My solution was to do a 2nd lookup for each transaction record and this works, but I would like to be able to sort the 1st transaction by the business name. I suspect the answer is to do a left-join transaction and get the name from that. However, I've never mastered left join and even though I looked it up again, I'm still not sure how it works. So, let me give an example of how I'm doing it now and maybe someone can suggest a better way.

$queryString = select * from tableB;//(truth be told, table B is called 'trips')
$result = mysqli....
while($array=mysqli_fetch_assoc($result)
{
 $queryString2 = select bizname from tableA where ID = $array[ID]//(tableA is called 'companies')

Any help would be appreciated.
Jeff Sandler

  • 4 Contributors
  • 3 Replies
  • 245 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by pty

Dani AI

Generated

Running a second query inside the fetch loop (as described) turns a single work unit into N+1 queries and is the main cause of slow scripts. is right to flag SELECT * — explicitly list only the columns you need (including the business name) to reduce I/O.

A single JOIN fetches the company name and lets you sort by it. Use an explicit JOIN and choose LEFT JOIN when you want to keep transactions that have no matching company row (INNER JOIN drops those). For example, pull the transaction columns you need plus the company name and order by that name:

SELECT t.id, t.date, t.amount, COALESCE(c.bizname,'(unknown)') AS bizname
FROM trips AS t
LEFT JOIN companies AS c
  ON t.company_id = c.ID
ORDER BY c.bizname, t.date DESC;

Notes and troubleshooting:

  • If some transactions legitimately lack a company record, LEFT JOIN keeps them; COALESCE gives a readable fallback. If you only want matched rows, use INNER JOIN instead.
  • Make sure companies.ID is a primary key/index and add an index on trips.company_id (or declare it a foreign key) so the join uses indexes. Run EXPLAIN on the query to confirm index use and check for filesorts or temporary tables.
  • For large result sets, paginate with LIMIT and an indexed sort key; avoid sorting millions of rows in one go. If you find ordering by bizname is a frequent, heavy operation, consider denormalizing (store company name on the transaction row) only after weighing update complexity.

This ties together ’s join idea and ’s pointer to JOINs, but adds practical indexing, NULL-handling, and pagination advice so the single-query approach scales.

Recommended Answers

All 3 Replies

Member Avatar for rproffitt

While I don't claim to solve this I always cringe when I see the "select *" in any query. There are a lot of articles why you always check for this and I don't know the table structure so I can't guess if you need all in the result set. Do you?

I've fixed many SQL speed problems on this alone. Worth checking.

Member Avatar for David_111

select a.bizname, b.col1, b.col2, b.col3 from table a, tableB where a.ID = b.ID;
I don't thinks an outer join is needed, and it's true a select * is a bad ...

Member Avatar for pty

It's annoying when people ask a straight up SQL question and wrap everything in $LANGUAGE.

To cut a long story short, what you need is a join.

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.