Including items from various tables in a query MySQL.

lxfdirs
lxfdirs used Ask the Experts™
on
Hi,

I need to generate a MySQL query based on a field from a table. My database contains 2 tables. On each record found by the query  I need to include data from the other table. This other data should be linked based on a specific field on the record found by the main query.
Hope I have explained myself.

Regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Your question is kinda vague, but you can do a new SELECT in the WHERE-clause.

E.g.:
SELECT name FROM table1 WHERE id=(SELECT id FROM table2 WHERE field=3)
Commented:
SELECT t1.FieldA, t2.FieldB FROM Table1 as t1, Table2 as t2 WHERE t1.KeyA=t2.KeyB
theGhost_k8Database Consultant
Commented:
watch out for join.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
Ah. I can see I read the question wrong.

yodercm's example with JOIN:

SELECT t1.FieldA, t2.FieldB FROM Table1 as t1 JOIN Table2 as t2 ON t1.KeyA=t2.KeyB

Commented:
Perhaps you could provide a bit more info and some sample data but I think you are trying to do this below ?


select * from tableA, tableB where tableA.ID = tableB.ID

Commented:
Don't use JOIN unless you absolutely have to, it's much messier and complicated to understand and debug.
Tomas Helgi JohannssonDatabase Administrator / Software Engineer

Commented:
            Hi!

To speed up the query make sure that you have indexes on both tables
that matches the columns you use in the where clauses of the query . :)

Regards,
    Tomas Helgi

Author

Commented:
Guys:

Thank you all for the suggestions.
I will be checking on them and come back to you with the accepted solution.

Regards
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial