Help needed finding data from 4 tables

Dev Shed Forums Sponsor:

December 17th, 2012, 06:30 AM

Marko2012

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 26 m 34 sec
Reputation Power: 0

Help needed finding data from 4 tables

Hi There

I have searched and tried to locate this answer before posting.

I have a MYSQL problem

I have 4 tables with lots of different columns.

All 4 tables have the same ID column, a unique reference number:
Each table has one Row that point to the same ID

My problem is:
I need query all 4 tables and get the row back using the ID column

How do i do this.

Im using

PHP Code:


		
			
 SELECT * FROM table1, table2, table3, table4 WHERE table1.ID = '$record'

This pulls other rows i don't want.

I just want the 4 rows with the same ID

How can i do this?

December 17th, 2012, 07:07 AM

cafelatte

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

Hm, where to begin...

OK. In your own time read up on JOINs and UNIONs. Then find out what DDLs are in relation to MySQL. Then, if you're still stuck, come back to us with a more structured question, and an indication of what the output should actually look like.

December 17th, 2012, 07:22 AM

Marko2012

Registered User

Join Date: Dec 2012

Posts: 3

Time spent in forums: 26 m 34 sec
Reputation Power: 0

Quote:

Originally Posted by cafelatte

Honest ive been reading and trying everything, im stuck.
I ve hit a brick wall.

All the JOIN answers relate to specific columns in each table,
but i have so many columns i can possible add all columns to the query. There has to be a simpler way.

I need a one liner to look up all 4 tables, and bring me back all the column data for that record: ID=165456

So something like:

Get ALL COLUMN DATA with ROW ID = 165456 from all the 4 TABLES

*****

There is only one row in each table with the same ID Number
Still trying to solve this one.

December 17th, 2012, 07:46 AM

cafelatte

Contributing User

Join Date: Mar 2008

Posts: 1,923

Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377

All of your tables share one, and only one, column in common. That's the criteria upon which they should be JOINed.

An inability to name all the columns in your query points to either poorly designed tables or chronic laziness.

December 17th, 2012, 08:19 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,373

Time spent in forums: 3 Months 1 Week 2 Days 6 h 54 m 49 sec
Reputation Power: 4140

normally i don't like to just provide a solution to what is potentially a homework assignment, but this one is rather obviously not a homework assignment, and since you say you've already read up on joins and the penny still hasn't dropped, here ya go...

Code:

SELECT table1.*
     , table2.* 
     , table3.* 
     , table4.* 
  FROM table1
INNER
  JOIN table2
    ON table2.id = table1.id
INNER
  JOIN table3
    ON table3.id = table1.id
INNER
  JOIN table4
    ON table4.id = table1.id
 WHERE table1.id = '$record'

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book