By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,664 Members | 1,999 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,664 IT Pros & Developers. It's quick & easy.

Query across multiple tables??

P: n/a
Hi,

I am new to mysql and need help on how to join tables. I have a database
which contains 4 tables, the main table contains information by date order
and the other 3 contain data also in date order. So I need to write a
query that retrieves all the information for one record, lets say I want to
query on the main table any entry that is for the 2004-01-06 and this date
is also in a field called 'Date' in the other tables, how do I go about
pulling back all the info for that entry across all the tables?

I have tried doing this:

WHERE main.Date = table2.Date AND table3.Date AND table4.date

however it returns loads of data when it should only pull back the one
entry.

Hope I have explained this clear enough, I am sure this is just standard
stuff to you guys but I would appreciate any help

Regards

RiGGa
Jul 20 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Rigga1 wrote:
Hi,

I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date order. So I need to write a query that retrieves all the information for one record, lets say I want to query on the main table any entry that is for the 2004-01-06 and this date is also in a field called ’Date’ in the other tables, how do I go about pulling back all the info for that entry across all the tables?

I have tried doing this:

WHERE main.Date = table2.Date AND table3.Date AND table4.date

however it returns loads of data when it should only pull back the one entry.

Hope I have explained this clear enough, I am sure this is just standard stuff to you guys but I would appreciate any help

Regards

RiGGa

You are on the right track. Just do it this way:
WHERE main.Date = table2.Date AND main.Date =table3.Date AND main.Date
=table4.date

--
http://www.dbForumz.com/ This article was posted by author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbForumz.com/mySQL-Query-...ict126358.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbForumz.com/eform.php?p=421105
Jul 20 '05 #2

P: n/a
steve wrote:
> RiGGa

You are on the right track. Just do it this way:
WHERE main.Date = table2.Date AND main.Date =table3.Date AND main.Date
=table4.date

Thanks that kind of worked but I didnt make myself clear enough in my
initial post. For the purpose of this thread lets say i have 3 tables:

Table1
Table2
Table3

Table 1 will always contain only one row for each record i.e. one entry for
the date 2004-01-05

Table 2 & 3 may contain several rows of data for the same date

What I want to be able to do is retrieve the data so that it displays all
the data from table 1 and the corresponding data from tables 2 & 3. What
is currently happening is that if table 2 (or table 3) contains 3 rows
then I get the information for table 1 repeated for 3 times. How can I do
it so that i get an output like this:

Table1: Table2: Table3:
----------------------------------------------------------------------
Date Field1 Field2 Date Field1 Field2 Date Field1 Field2
Date Field1 Field2 Date Field1 Field2
Date Field1 Field2 Date Field1 Field2
Date Field1 Field2
Date Field1 Field2
Hope this is explained better. Thanks for your help

RiGGa
Jul 20 '05 #3

P: n/a
Rigga wrote:

What I want to be able to do is retrieve the data so that it displays all
the data from table 1 and the corresponding data from tables 2 & 3. What
is currently happening is that if table 2 (or table 3) contains 3 rows
then I get the information for table 1 repeated for 3 times.


Yes, that is correct. That is what you will get. You have to deal with
it on the application side.

Databases return rows, rows will always have a value for each column.
That value can be empty, null or any other value. But you can't get rows
that have different amount of columns than others. ( Unless you make
multiple queries and for example get data for one table at a time, which
is usually slower )

You could perhaps make the columns have empty values, so that result
would look like what you wanted, but that would make the query look a
lot more complex and propably slow it too. I have worked with databases
a couple of years and it would be hard or impossible for me to tell how
to do it, because it would be so complex. So I don't recommend it.
Jul 20 '05 #4

P: n/a
Aggro wrote:
Rigga wrote:

What I want to be able to do is retrieve the data so that it displays all
the data from table 1 and the corresponding data from tables 2 & 3. What
is currently happening is that if table 2 (or table 3) contains 3 rows
then I get the information for table 1 repeated for 3 times.


Yes, that is correct. That is what you will get. You have to deal with
it on the application side.

Databases return rows, rows will always have a value for each column.
That value can be empty, null or any other value. But you can't get rows
that have different amount of columns than others. ( Unless you make
multiple queries and for example get data for one table at a time, which
is usually slower )

You could perhaps make the columns have empty values, so that result
would look like what you wanted, but that would make the query look a
lot more complex and propably slow it too. I have worked with databases
a couple of years and it would be hard or impossible for me to tell how
to do it, because it would be so complex. So I don't recommend it.

I have read up a little on LEFT JOIN and the examples they show have results
returning as NULL like I would want however on following the example code
and applying it to my database it did not return NULL

I am a bit at a loss now how I am going to query this data and display the
results on screen, the overall aim is to run a query against the tables and
pull out the data relevant for a particular date and then stick it on a web
page using PHP. If however the database is returning data in the columns
that is not really there (i.e. when table 3 contains more 5 records and
table 2 only 3 and table 1 will only ever have 1 row so I get back 5 rows
with data in every column which is wrong..) How would I know where the data
really ended and was not just repeated to make up the lines to what was in
table 3??

Thanks

Jul 20 '05 #5

P: n/a
Rigga wrote:
I have read up a little on LEFT JOIN and the examples they show have results
returning as NULL like I would want however on following the example code
and applying it to my database it did not return NULL
That works the other way around.
Instead of
value value value
null value value
null value value
null null value

You would get:
value value value
value value null
value value null
value null null

So it would be pretty much opposite of what you want.
I am a bit at a loss now how I am going to query this data and display the
results on screen, the overall aim is to run a query against the tables and
pull out the data relevant for a particular date and then stick it on a web
page using PHP. If however the database is returning data in the columns
that is not really there (i.e. when table 3 contains more 5 records and
table 2 only 3 and table 1 will only ever have 1 row so I get back 5 rows
with data in every column which is wrong..) How would I know where the data
really ended and was not just repeated to make up the lines to what was in
table 3??


That would be a good question for the php group. But I give you a short
answer.

If you have for example data like this:
"2004-06-01" "car" "2004-06-01" "horse"
"2004-06-01" "car" "2004-06-01" "cow"
"2004-06-02" "bike" "2004-06-02" "bird"

And you want to show the results like this (each date gets printed only
one, and under it we will print value froms second table):

2004-06-01 - car
- horse
- cow
2004-06-02 - bike
- bird

Then you could do on the php side something like:

-------- not tested -----------------
$date = "";
foreach( rows as row )
{
if( row->date != $date )
{
// Date has changed, print it
echo row->date . " - " . row->field1 . "\n";
$date = row->date;
}

// Print normal data
echo " - " . row->field2 . "\n";
}
-------- not tested -----------------

Now, since you have more tables, this will get more complex also, but
that's the idea how to do it (well one way to do it).
Jul 20 '05 #6

P: n/a
Aggro wrote:
$date = "";
foreach( rows as row )
{
if( row->date != $date )
{
// Date has changed, print it
echo row->date . " - " . row->field1 . "\n";
$date = row->date;
}

// Print normal data
echo " - " . row->field2 . "\n";
}


Oh, and please remember to use "order by date" at the end of your query,
or that code won't work correctly ;)
Jul 20 '05 #7

P: n/a
Aggro wrote:
Aggro wrote:
$date = "";
foreach( rows as row )
{
if( row->date != $date )
{
// Date has changed, print it
echo row->date . " - " . row->field1 . "\n";
$date = row->date;
}

// Print normal data
echo " - " . row->field2 . "\n";
}


Oh, and please remember to use "order by date" at the end of your query,
or that code won't work correctly ;)

Thanks thats helped a lot. I appreciate you taking the time to help me.

Regards

RiGGa
Jul 20 '05 #8

P: n/a
Rigga wrote:
I have read up a little on LEFT JOIN and the examples they show have results
returning as NULL like I would want however on following the example code
and applying it to my database it did not return NULL
LEFT JOIN doesn't sound like what you need in this case. It would be
useful in cases where you want, for instance, to fetch date values from
table1 for which there aren't _any_ corresponding values in table2 or
table3.
I am a bit at a loss now how I am going to query this data and display the
results on screen, the overall aim is to run a query against the tables and
pull out the data relevant for a particular date and then stick it on a web
page using PHP. If however the database is returning data in the columns
that is not really there (i.e. when table 3 contains more 5 records and
table 2 only 3 and table 1 will only ever have 1 row so I get back 5 rows
with data in every column which is wrong..) How would I know where the data
really ended and was not just repeated to make up the lines to what was in
table 3??


One of the most frequent misconceptions I see people making in SQL
programming is that every task must be accomplished with a single SQL
statement. It is often more straightforward to do it in more than one
statement.

For example, one alternative for your situation would be to do the job
in three queries instead of one.

1) Get a list of all dates in table1 that have at least one
corresponding row in either table2 or table3:
select distinct T1.Date
from table1 T1 inner join table2 T2 on T1.Date = T2.Date
inner join table3 T3 on T1.Date = T3.Date

2) Run a loop for each successive Date value in that result set from table1.

3) Place the successive values of Date in place of the ? and execute
these queries for each iteration through the loop:
select T2.Date from table2 T2 where T2.Date = ?;
select T3.Date from table3 T3 where T3.Date = ?;
Then you have, for each value of table1.Date, two separate result sets
that comprise the lists from table2 and table3, respectively. Format
them in your web page as needed.

Regards,
Bill K.
Jul 20 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.