Gossamer Forum
Home : General : Databases and SQL :

query help

Quote Reply
query help
I hope this is the right place to ask this. I'm using perl and mysql and I'm having trouble figuring out a query. I have three tables - Activities, Sponsors and Links. In the activities table I want to select all the rows for a given date range. That part is simple and done. But, in this table are three fields in particular for each record where I need to get info from other tables, sponsor1id, sponsor2id and linkid. The first two of these are id's that relate to separate records in the Sponsors table and as part of the query I want to get the SponsorName from the Sponsor table for those two columns. For the linkid field, I need to pull the url from the Links table based upon that records linkid. I'm totally baffled. Can this be done in a single query.

I know normally if I wanted to pull data from two tables, I would do something like

SELECT fields from Activities,Links where Activities.linkid = Links.linkid AND Activities.date = range

But what has me baffled is that I want to pull two records from the Sponsors and one from the Links table for each result returned.

Also, will doing it this way bog down the server / slow down the query dramatically... I figured because there are a lot of records, probably half a million when I finish the first cut, I didn't want to repeat so much data over and over and over again so I created "lookup tables".

If anyone can help I'd be most appreciative.
Quote Reply
Re: [JerryP] query help In reply to
1) I would recommend using JOIN rather than the condition in the WHERE clause.

Example:

SELECT tbl1.*, tbl2.*, tbl3.*
FROM table1 tbl1 LEFT JOIN table2 tbl2 ON tbl2.FIELD = tbl1.FIELD
LEFT JOIN table3 tbl3 ON tbl3.FIELD = tbl1.FIELD
WHERE (tbl1.DATE BETWEEN 'SOMEDATE' AND 'SOMEOTHERDATE')

2) Try using the BETWEEN operator or you could use other MySQL functions like MONTH(COLUMN) to get the range that you want.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] query help In reply to
Thank your for the assist. Let me see if I can understand your suggestion but I think there is still a problem because I get an error message... Here's my query using my tablenames and such and for simplicity I'll ignore the Links table for now... my problem is I want to get two entries from the Sponsors table for each row... one for sponsor1id and one for sponsor2id. Here's my query



SELECT Activities.name,Activities.date,Sponsors.name,Sponsors.name

FROM Activities

LEFT JOIN Sponsors ON Sponsors.sponsorid = Activities.sponsor1id

LEFT JOIN Sponsors ON Sponsors.sponsorid = Activities.sponsor2id

WHERE Activities.week = 1



When I run this query I get the following error message

ERROR 1066: Not unique table/alias: 'Sponsors'

This is what has me baffled. I don't want to have to run a query on each record, I could literally end up with hundreds of them to get one page for the site. Is there something I'm missing or doing wrong, or can this just not be done?



And is a LEFT JOIN more efficient (server load wise) than matching in the where clause or is it just a personal programming preference?

Last edited by:

JerryP: Aug 29, 2002, 3:00 PM
Quote Reply
Re: [JerryP] query help In reply to
Uh...you don't need two JOINS if you are only "joining" two tables, but it was a bit confusing in your original request since you stated that you are "joining" three tables. Anyway, revise the example query I gave you by removing the second LEFT JOIN.

YES, using JOINS at least in most SQL database applications, like MySQL, SQL Server, and Postgres (anything but Oracle, which doesn't support "JOINS"), is more efficient in terms of cycles and resource use than using conditions in the WHERE clause.
========================================
Buh Bye!

Cheers,
Me
Quote Reply
Re: [Stealth] query help In reply to
I understand what you are saying, but maybe I'm confusing you. I took out the third table in my second post to make the example easier. My problem is still though, when I get record 1 from table1, in that record there are two fields, one is sponsor1id and the other is sponsor2id. They are two different columns and will generally always be present for each record in table 1. They are both id codes that relate to records (always different) in table 2. So, for example, in the first row of data I have sponsor1id = 145 and sponsor2id = 388. They both correspond to records in table 2. How do I get the query to retrieve the name field in table 2 for both columns in table 1. Effectively it needs to do 2 lookups in table 2, not 1. Does that make sense?
Quote Reply
Re: [JerryP] query help In reply to
Hi,

I guess you want to do this:

select


table1.col1,

table1.col2,

(select table2.col2 from table2 where table1.col1=table2.col1),

(select table2.col2 from table2 where table1.col2=table2.col1)


from table1

(This is true if Table2.col1 is unique)
Quote Reply
Re: [videssos] query help In reply to
I appreciate that suggestion, I ended up having a programmer write a lookup module for me to accomplish something else and it ended up fitting the bill quite nicely for what I wanted to do here.