473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting data from two different tables in one query

HaLo2FrEeEk
404 256MB
I have two different tables which havea different number of columns. One of the tables gets a new row every day and is populated with a st of 4 IDs from another table, along with today's date and an expiration date. The other table also has today's date and an expiration date, but it only gets one ID from the other table, and it is updated once a week.

I need to get the most recent column from both of these tables, and I'd like to do it in a single query. My tables are called current_daily and current_weekly. Here are the columns:

current_daily:
date, daily1, daily2, daily3, daily4, expiration

current_weekly:
date, weekly, expiration

I tried using a simple JOIN query:

SELECT * FROM current_daily D, current_weekly W ORDER BY D.date DESC, W.date DESC LIMIT 0, 1

And it did get the most recent row from both of the tables in a single query, but the problem is that there is ambiguity between the date and expiration columns, so that when I use mysql_fetch_assoc() in PHP, I only get one of each of those columns.

Now, I could change the names of the date and expiration columns so that they're different between the two tables, but before I do that I want to know if there is a easier way. I was looking at LEFT JOIN, but there aren't any columns that are the same between both tables every time. Date and Expiration will be the same between both only once a week, so this:

SELECT * FROM current_daily D LEFT OUTER JOIN current_weekly W ON D.date = W.date ORDER BY D.date DESC LIMIT 0, 1

Will return NULL for the weekly columns for every day except sunday, when the new weekly row is added.

Any suggestions?
Mar 20 '11 #1
6 3182
dgreenhouse
250 Expert 100+
EDIT: I just fully read your post, so I'll have to think a moment.
(I also have to get some sleep... Been up all night...)

You'll have to clear the ambiguity by prepending the table name.

Expand|Select|Wrap|Line Numbers
  1. select d.date,daily1,daily2,daily3,daily4,d.expiration,w.date,weekly,w.expiration
  2. from current_daily d, current_weekly w
  3. order by d.date desc, w.date desc limit 0,1
  4.  
As far as the nulls are concerned, that's what you're telling the query to do
based on the table structure.

Would I be correct in assuming that you want a summary (weekly) at the end of the daily rows?

If so, it can be done in a query, but it would be rather complex.
Mar 20 '11 #2
HaLo2FrEeEk
404 256MB
You would be correct. The weekly table only gets a new row every sunday, so if I query this on, say tuesday, I want to return tuesday's daily row, and sunday's weekly row.

Can I see the query for it?
Mar 20 '11 #3
Rabbit
12,516 Expert Mod 8TB
Does it have to be in one query? Can't you run two different queries?
Mar 20 '11 #4
dgreenhouse
250 Expert 100+
As Rabbit notes, it would be simpler to do it in two queries and then combine the data in code.

If you want to be an SQL purist, it'll take more effort and it's probably not worth it.

I'm still waking up and won't be back active for awhile! :)
Mar 20 '11 #5
HaLo2FrEeEk
404 256MB
I just figured it's easier to let the database do the work. Right now I am using two queries, and it works, I just wanted to see if there was a better way.
Mar 21 '11 #6
Rabbit
12,516 Expert Mod 8TB
Well, any other method would cause additional overhead on the SQL server. And whether or not that additional overhead is worth it would have to be evaluated on a case by case basis.
Mar 21 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: manning_news | last post by:
Using SQL2000. How do I format my select statement to choose one out of 24 different tables? Each table is slightly different and I was hoping I could use one select statement and format it...
2
by: narasingarao | last post by:
Hi to group, I'm a student of M.C.A. from B.I.T. Ranchi...I'm in my project period here i have to migrate the MS-Access database table to Oracle data base tables...so, please help me in getting...
4
by: John | last post by:
Hi All, I have a problem making changes to a query. The query is extracting data from three different tables, but when I run the query in a form or datasheet view I can't make any changes to the...
10
by: Marizel | last post by:
I'm not sure there's an easy solution to this, but thought I'd ask. I often find myself with a query which I'd like to reuse, but with a different datasource. These datasources generally have...
9
by: Hi5 | last post by:
Hi, Any Idea how, I can make an Insert statement to insert data into 6 different tables, that are all holding all data of my database? Is there any example? I would be grateful if you could...
1
by: Stu | last post by:
Hi, I am trying to extract the values from a few specific fields in a word document from VB. I have now opened the doc and can loop through all the paragraphs...but none of the tables are...
1
by: suzieq | last post by:
This might be total newbie, and I apologize if it is, but it is making me insane. I have a database with lots of tables, queries and reports (someone else made the database and I am now taking...
1
by: empiresolutions | last post by:
Hello Fellow Programmers, I have an issue with a very large PHP page that calls over 20 different MySql Statements. This page is loading somewhat slow and i want to speed things up. I have looked...
0
by: Jerms | last post by:
Hello all, I've been using this site quite a bit since starting my project and have found it very helpful. I have run into a roadblock though that I cant seem to scrounge up a solution to. I...
7
by: Dresse | last post by:
Hello again I am having some trouble getting the right data out of my database, maybe any of you can help me get the right data. My Tables: tblReport ReportID (pk) Date
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.