Trying to make a valid JOIN or SQL Statement

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Trying to make a valid JOIN or SQL Statement

Paul Melia
Hi List,

I could really use a good shove in the right direction on this one.
I have a working solution using nested inlines but I am hoping that  
an optimized SQL query would be faster and more efficient.
Any help or pointers would be thanked profusely.

Situation (simplified):

Table 1 - Reports (800 reports)

Field Value
id key
status numeric value
report_title name of the report
reviewer1ID record id for reviewer in table 2
reviewer2ID record id for reviewer in table 2
reviewer3ID record id for reviewer in table 2


Table 2 - Reviewers (50 reviewers)

Field Value
id key
firstname reviewer's first name
lastname reviewer's last name


I need to generate a list with rows that look like this:

Report #1: [Report Title] (value from Table 1)
Reviewer 1: [reviewer 1 first name] [reviewer 1 last name] (values  
from Table 2 via table1.reviewer1ID)
Reviewer 2: [reviewer 2 first name] [reviewer 2 last name] (values  
from Table 2 via table1.reviewer2ID)
Reviewer 3: [reviewer 3 first name] [reviewer 3 last name] (values  
from Table 2 via table1.reviewer3ID)

Each report can have up to three reviewers assigned to it and the  
three reviewers can be any combination of reviewers from Table 2.

I tried a straight join (in CocoaMySQL) and got it to sorta work when  
I fed the SQL statement  a specific reviewerID value but I want to  
perform the search against the status field in Table 1 and then have  
the JOIN (if that is the right SQL function to use here) retrieve the  
corresponding first and last names for each of reviewers present in  
the found record.

Am I out of bounds here? (My first attempt at a SQL version within a  
Lasso page crashed Lasso something fierce)

Thank you so very much,
Paul




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Trying to make a valid JOIN or SQL Statement

decorior
So what happens with

select reviewers.id, reviewers.firstname,  
reviewers.lastname,reports.* from reports,reviewers where  
(reports.reviewer1ID = reviewers.id or reports.reviewer2ID =  
reviewers.id or reports.reviewer3ID = reviewers.id)




On May 16, 2006, at 10:12 PM, Paul Melia wrote:

> Hi List,
>
> I could really use a good shove in the right direction on this one.
> I have a working solution using nested inlines but I am hoping that  
> an optimized SQL query would be faster and more efficient.
> Any help or pointers would be thanked profusely.
>
> Situation (simplified):
>
> Table 1 - Reports (800 reports)
>
> Field Value
> id key
> status numeric value
> report_title name of the report
> reviewer1ID record id for reviewer in table 2
> reviewer2ID record id for reviewer in table 2
> reviewer3ID record id for reviewer in table 2
>
>
> Table 2 - Reviewers (50 reviewers)
>
> Field Value
> id key
> firstname reviewer's first name
> lastname reviewer's last name
>
>
> I need to generate a list with rows that look like this:
>
> Report #1: [Report Title] (value from Table 1)
> Reviewer 1: [reviewer 1 first name] [reviewer 1 last name] (values  
> from Table 2 via table1.reviewer1ID)
> Reviewer 2: [reviewer 2 first name] [reviewer 2 last name] (values  
> from Table 2 via table1.reviewer2ID)
> Reviewer 3: [reviewer 3 first name] [reviewer 3 last name] (values  
> from Table 2 via table1.reviewer3ID)
>
> Each report can have up to three reviewers assigned to it and the  
> three reviewers can be any combination of reviewers from Table 2.
>
> I tried a straight join (in CocoaMySQL) and got it to sorta work  
> when I fed the SQL statement  a specific reviewerID value but I  
> want to perform the search against the status field in Table 1 and  
> then have the JOIN (if that is the right SQL function to use here)  
> retrieve the corresponding first and last names for each of  
> reviewers present in the found record.
>
> Am I out of bounds here? (My first attempt at a SQL version within  
> a Lasso page crashed Lasso something fierce)
>
> Thank you so very much,
> Paul
>
>
>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription: http://www.listsearch.com/ 
> lassotalk.lasso?manage


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Trying to make a valid JOIN or SQL Statement

stevepiercy
In reply to this post by Paul Melia
This sounds just like a join I use, whereas I have soccer games and 3 referees (your reviewers) assigned to each game (your reports).  Try this:

select reports.id, reports.report_title,
 rev1_tmp.firstname as r1fn,
 rev1_tmp.lastname as r1ln,
 rev2_tmp.firstname as r2fn,
 rev2_tmp.lastname as r2ln,
 rev3_tmp.firstname as r3fn,
 rev3_tmp.lastname as r3ln
FROM reports
Left Join reviewers rev1_tmp ON reports.reviewer1ID=rev1_tmp.id
Left Join reviewers rev2_tmp ON reports.reviewer2ID=rev2_tmp.id
Left Join reviewers rev3_tmp ON reports.reviewer3ID=rev3_tmp.id

This should return an array of records that you can format in HTML as you like.

FYI, rev#_tmp is actually an unique alias for the table from which you are pulling the first and last names of the corresponding reviewers.

Also try this:
<http://aquafold.com/>
It helps you learn sql much easier than CrashMySQL.

Hope this helps.

--steve


On Wednesday, May 17, 2006, [hidden email] (Paul Melia) pronounced:

>Hi List,
>
>I could really use a good shove in the right direction on this one.
>I have a working solution using nested inlines but I am hoping that  
>an optimized SQL query would be faster and more efficient.
>Any help or pointers would be thanked profusely.
>
>Situation (simplified):
>
>Table 1 - Reports (800 reports)
>
>Field          Value
>id             key
>status         numeric value
>report_title       name of the report
>reviewer1ID        record id for reviewer in table 2
>reviewer2ID        record id for reviewer in table 2
>reviewer3ID        record id for reviewer in table 2
>
>
>Table 2 - Reviewers (50 reviewers)
>
>Field      Value
>id         key
>firstname  reviewer's first name
>lastname   reviewer's last name
>
>
>I need to generate a list with rows that look like this:
>
>Report #1: [Report Title] (value from Table 1)
>Reviewer 1: [reviewer 1 first name] [reviewer 1 last name] (values  
>from Table 2 via table1.reviewer1ID)
>Reviewer 2: [reviewer 2 first name] [reviewer 2 last name] (values  
>from Table 2 via table1.reviewer2ID)
>Reviewer 3: [reviewer 3 first name] [reviewer 3 last name] (values  
>from Table 2 via table1.reviewer3ID)
>
>Each report can have up to three reviewers assigned to it and the  
>three reviewers can be any combination of reviewers from Table 2.
>
>I tried a straight join (in CocoaMySQL) and got it to sorta work when  
>I fed the SQL statement  a specific reviewerID value but I want to  
>perform the search against the status field in Table 1 and then have  
>the JOIN (if that is the right SQL function to use here) retrieve the  
>corresponding first and last names for each of reviewers present in  
>the found record.
>
>Am I out of bounds here? (My first attempt at a SQL version within a  
>Lasso page crashed Lasso something fierce)
>
>Thank you so very much,
>Paul
>
>
>
>
>------------------------------
>Lasso Support: http://support.omnipilot.com/
>Search the list archives: http://www.listsearch.com/lassotalk.lasso
>Manage your list subscription:  
>http://www.listsearch.com/lassotalk.lasso?manage
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy               Web Site Builder               Soquel, CA
<[hidden email]>                   <http://www.StevePiercy.com>

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Trying to make a valid JOIN or SQL Statement

Paul Melia
In reply to this post by Paul Melia

On May 17, 2006, at 12:48 AM, Deco Rior wrote:

> So what happens with
>
> select reviewers.id, reviewers.firstname,  
> reviewers.lastname,reports.* from reports,reviewers where  
> (reports.reviewer1ID = reviewers.id or reports.reviewer2ID =  
> reviewers.id or reports.reviewer3ID = reviewers.id)

It close but not quite there -  I get back the data for 1 record  as  
three rows (one for each reviewerID).
It also does not seem to want to run through the entire data set for  
some strange reason.

But I am up way too late and will have to approach this with a rested  
mind in later this morning.

Thank you very much for the quick response,
Paul

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Trying to make a valid JOIN or SQL Statement

Paul Melia
In reply to this post by Paul Melia
Thank you, Steve - I'll give it a try over the morning coffee.
I just download Aquastudio on your recommendation and will explore it  
as well.

All the best,
Paul

On May 17, 2006, at 2:47 AM, Steve Piercy - Web Site Builder wrote:

> This sounds just like a join I use, whereas I have soccer games and  
> 3 referees (your reviewers) assigned to each game (your reports).  
> Try this:
>
> select reports.id, reports.report_title,
>  rev1_tmp.firstname as r1fn,
>  rev1_tmp.lastname as r1ln,
>  rev2_tmp.firstname as r2fn,
>  rev2_tmp.lastname as r2ln,
>  rev3_tmp.firstname as r3fn,
>  rev3_tmp.lastname as r3ln
> FROM reports
> Left Join reviewers rev1_tmp ON reports.reviewer1ID=rev1_tmp.id
> Left Join reviewers rev2_tmp ON reports.reviewer2ID=rev2_tmp.id
> Left Join reviewers rev3_tmp ON reports.reviewer3ID=rev3_tmp.id
>
> This should return an array of records that you can format in HTML  
> as you like.
>
> FYI, rev#_tmp is actually an unique alias for the table from which  
> you are pulling the first and last names of the corresponding  
> reviewers.
>
> Also try this:
> <http://aquafold.com/>
> It helps you learn sql much easier than CrashMySQL.
>
> Hope this helps.
>
> --steve
>
>
> On Wednesday, May 17, 2006, [hidden email] (Paul Melia)  
> pronounced:
>
>> Hi List,
>>
>> I could really use a good shove in the right direction on this one.
>> I have a working solution using nested inlines but I am hoping that
>> an optimized SQL query would be faster and more efficient.
>> Any help or pointers would be thanked profusely.
>>
>> Situation (simplified):
>>
>> Table 1 - Reports (800 reports)
>>
>> Field          Value
>> id             key
>> status         numeric value
>> report_title       name of the report
>> reviewer1ID        record id for reviewer in table 2
>> reviewer2ID        record id for reviewer in table 2
>> reviewer3ID        record id for reviewer in table 2
>>
>>
>> Table 2 - Reviewers (50 reviewers)
>>
>> Field      Value
>> id         key
>> firstname  reviewer's first name
>> lastname   reviewer's last name
>>
>>
>> I need to generate a list with rows that look like this:
>>
>> Report #1: [Report Title] (value from Table 1)
>> Reviewer 1: [reviewer 1 first name] [reviewer 1 last name] (values
>> from Table 2 via table1.reviewer1ID)
>> Reviewer 2: [reviewer 2 first name] [reviewer 2 last name] (values
>> from Table 2 via table1.reviewer2ID)
>> Reviewer 3: [reviewer 3 first name] [reviewer 3 last name] (values
>> from Table 2 via table1.reviewer3ID)
>>
>> Each report can have up to three reviewers assigned to it and the
>> three reviewers can be any combination of reviewers from Table 2.
>>
>> I tried a straight join (in CocoaMySQL) and got it to sorta work when
>> I fed the SQL statement  a specific reviewerID value but I want to
>> perform the search against the status field in Table 1 and then have
>> the JOIN (if that is the right SQL function to use here) retrieve the
>> corresponding first and last names for each of reviewers present in
>> the found record.
>>
>> Am I out of bounds here? (My first attempt at a SQL version within a
>> Lasso page crashed Lasso something fierce)
>>
>> Thank you so very much,
>> Paul
>>
>>
>>
>>
>> ------------------------------
>> Lasso Support: http://support.omnipilot.com/
>> Search the list archives: http://www.listsearch.com/lassotalk.lasso
>> Manage your list subscription:
>> http://www.listsearch.com/lassotalk.lasso?manage
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy               Web Site Builder               Soquel, CA
> <[hidden email]>                   <http://www.StevePiercy.com>
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:
> http://www.listsearch.com/lassotalk.lasso?manage


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Trying to make a valid JOIN or SQL Statement

Michael Coninx
In reply to this post by Paul Melia
Paul,

Your two tables are in a many to many relationship, in relational theory
this means you should
normalize your data first before continuing.

In this case:
Table 1 - Report
report_ID
status
title

Table 2 - Reviewer
reviewer_id
firstname
lastname

Table 3 - Report_Review
report_id
reviewer_id

You can now have any number of reviewers for a report, not limited to
the three
in your original solution.

select p.status, p.title, r.firstname, r.lastname from report_review as
rr left join report as p on (rr.report_id = p.report_id) left join
reviewer as r on (rr.reviewer_id = r.reviewer_id) where ...

In the where clause you can use order by or group by to retrieve the
info in different ways.

With kind regards,

Michael


Paul Melia wrote:

> Hi List,
>
> I could really use a good shove in the right direction on this one.
> I have a working solution using nested inlines but I am hoping that  
> an optimized SQL query would be faster and more efficient.
> Any help or pointers would be thanked profusely.
>
> Situation (simplified):
>
> Table 1 - Reports (800 reports)
>
> Field            Value
> id                key
> status            numeric value
> report_title        name of the report
> reviewer1ID        record id for reviewer in table 2
> reviewer2ID        record id for reviewer in table 2
> reviewer3ID        record id for reviewer in table 2
>
> Table 2 - Reviewers (50 reviewers)
>
> Field        Value
> id            key
> firstname    reviewer's first name
> lastname    reviewer's last name
>
> I need to generate a list with rows that look like this:
>
> Report #1: [Report Title] (value from Table 1)
> Reviewer 1: [reviewer 1 first name] [reviewer 1 last name] (values  
> from Table 2 via table1.reviewer1ID)
> Reviewer 2: [reviewer 2 first name] [reviewer 2 last name] (values  
> from Table 2 via table1.reviewer2ID)
> Reviewer 3: [reviewer 3 first name] [reviewer 3 last name] (values  
> from Table 2 via table1.reviewer3ID)
>
> Each report can have up to three reviewers assigned to it and the  
> three reviewers can be any combination of reviewers from Table 2.
>
> I tried a straight join (in CocoaMySQL) and got it to sorta work when  
> I fed the SQL statement  a specific reviewerID value but I want to  
> perform the search against the status field in Table 1 and then have  
> the JOIN (if that is the right SQL function to use here) retrieve the  
> corresponding first and last names for each of reviewers present in  
> the found record.
>
> Am I out of bounds here? (My first attempt at a SQL version within a  
> Lasso page crashed Lasso something fierce)
>
> Thank you so very much,
> Paul
>
> ------------------------------
> Lasso Support: http://support.omnipilot.com/
> Search the list archives: http://www.listsearch.com/lassotalk.lasso
> Manage your list subscription:  
> http://www.listsearch.com/lassotalk.lasso?manage
>


------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage
Reply | Threaded
Open this post in threaded view
|

Re: Trying to make a valid JOIN or SQL Statement

Ke Carlton
In reply to this post by Paul Melia
How about the below, obviously you can add the additional fields.

Select r.report_title,
        r1.firstname as ref1,
        r2.firstname as ref2,
        r3.Firstname as ref3

        from reports as r

        left join reviewers as r1 on r.reviewer1ID = r1.id,
        left join reviewers as r2 on r.reviewer2ID = r2.id,
        left join reviewers as r3 on r.reviewer3ID = r3.id


Ke.




-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf
Of Paul Melia
Sent: 17 May 2006 05:13
To: [hidden email]
Subject: Trying to make a valid JOIN or SQL Statement

Hi List,

I could really use a good shove in the right direction on this one.
I have a working solution using nested inlines but I am hoping that  
an optimized SQL query would be faster and more efficient.
Any help or pointers would be thanked profusely.

Situation (simplified):

Table 1 - Reports (800 reports)

Field Value
id key
status numeric value
report_title name of the report
reviewer1ID record id for reviewer in table 2
reviewer2ID record id for reviewer in table 2
reviewer3ID record id for reviewer in table 2


Table 2 - Reviewers (50 reviewers)

Field Value
id key
firstname reviewer's first name
lastname reviewer's last name


I need to generate a list with rows that look like this:

Report #1: [Report Title] (value from Table 1)
Reviewer 1: [reviewer 1 first name] [reviewer 1 last name] (values  
from Table 2 via table1.reviewer1ID)
Reviewer 2: [reviewer 2 first name] [reviewer 2 last name] (values  
from Table 2 via table1.reviewer2ID)
Reviewer 3: [reviewer 3 first name] [reviewer 3 last name] (values  
from Table 2 via table1.reviewer3ID)

Each report can have up to three reviewers assigned to it and the  
three reviewers can be any combination of reviewers from Table 2.

I tried a straight join (in CocoaMySQL) and got it to sorta work when  
I fed the SQL statement  a specific reviewerID value but I want to  
perform the search against the status field in Table 1 and then have  
the JOIN (if that is the right SQL function to use here) retrieve the  
corresponding first and last names for each of reviewers present in  
the found record.

Am I out of bounds here? (My first attempt at a SQL version within a  
Lasso page crashed Lasso something fierce)

Thank you so very much,
Paul




------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage

------------------------------
Lasso Support: http://support.omnipilot.com/
Search the list archives: http://www.listsearch.com/lassotalk.lasso
Manage your list subscription:  
http://www.listsearch.com/lassotalk.lasso?manage