Page 1 of 1

Related Question, Need help asap [SUBQUERY HELP]

Posted: Tue Jul 13, 2010 4:37 pm
by bla5e
I am trying to populate an HTML table with data from 2 SQL tables, that reference each other (autorenewals_schedules & autorenewals_schedules_intervals)
Column | Type | Modifiers
-----------+---------+--------------------------------------------------------------------
id | integer | not null default nextval('autorenewal_schedules_id_seq'::regclass)
productid | integer | not null
effortid | integer | not null
Column | Type | Modifiers
------------+---------+------------------------------------------------------------------------------
scheduleid | integer | not null
interval | integer | not null
templateid | integer | not null
id | integer | not null default nextval('autorenewal_schedules_intervals_id_seq'::regclass)
How would a get the interval and templateid from autorenewals_schedules_intervals as a subquery for autorenewals_schedules?
ex: [my query so far..]
viewtopic.php?f=1&t=118615

Code: Select all

$query = "SELECT id,effortid,productid,  
                (SELECT interval FROM autorenewal_schedules_intervals WHERE scheduleid=autorenewal_schedules.id) AS interval,
                (SELECT description FROM templates WHERE id=autorenewals_schedules_intervals.id) AS template
              FROM autorenewal_schedules                                                                                                                       
              WHERE productid={$_SESSION['renewals_pub']}";

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Tue Jul 13, 2010 5:14 pm
by bla5e
My 2 subqueries i need looped through

Code: Select all

SELECT description FROM templates WHERE id=autorenewal_schedules_intervals.templateid

SELECT interval FROM autorenewal_schedules_intervals WHERE scheduleid=autorenewal_schedules.id

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 10:35 am
by bla5e
can anyone help please?! i think i need to use a JOIN?? :crazy:

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 10:56 am
by buckit
I am not that great with SQL but this is what I use when I am trying to get joining data from multiple tables.

Code: Select all

$query = "SELECT asi.interval, t.description, as.id, as.effortid, as.productid 
FROM autorenewal_schedules_intervals asi, templates t, autorenewal_schedules as 
WHERE asi.scheduleid = as.id AND
	  t.id = asi.id AND
	  as.productid={$_SESSION['renewals_pub']}";
This probably wont work for exactly what you are trying to do... but maybe it will send you in the right direction...

sorry if this doesnt help at all!

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 11:17 am
by bla5e
buckit wrote:I am not that great with SQL but this is what I use when I am trying to get joining data from multiple tables.

Code: Select all

$query = "SELECT asi.interval, t.description, as.id, as.effortid, as.productid 
FROM autorenewal_schedules_intervals asi, templates t, autorenewal_schedules as 
WHERE asi.scheduleid = as.id AND
	  t.id = asi.id AND
	  as.productid={$_SESSION['renewals_pub']}";
This probably wont work for exactly what you are trying to do... but maybe it will send you in the right direction...

sorry if this doesnt help at all!
This worked!! WEll slightly...

It works if there is only 1 record in autorenewal_schedules_intervals that reference to autorenewal_schedules.. But if there is 2, it doesnt work.
Examples:

Code: Select all

 scheduleid | interval | templateid | id 
------------+----------+------------+----
          3 |       15 |          5 |  5
          4 |        3 |          5 |  7
          4 |       15 |          5 |  8
scheduleid 3's data will popualte in my table, but scheduleid 4 will not..? :banghead:
also i need to DESCRIPTION from table Templates where id=asi.templateid

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 11:26 am
by buckit
Ahh! I see.

well... its beyond my knowledge of SQL.. at this point I would cut my losses and opt for nested selects. not the most efficient way of doing it..

as you are looping through the results of your SELECT statement, check the autorenewal_schedules_intervals table to see if there are more entries matching the templateid in your primary results... if so then loop through them.

so in side your loop have something like:

SELECT * FROM autorenewal_schedules_intervals WHERE templateid = ".the templateid from previous select results." AND scheduleid != ".the scheduleid from previous select results."

the second WHERE statement "scheduleid !=" is just saying WHERE scheduleid does not equal the scheduleid I already have from my previous SELECT.

make sense?

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 11:33 am
by bla5e
ok i got it to populate my table with the data from all the SQL tables and populates with correct data, couple changes i want made though...
- It populated the table if there is duplicate ID's from autorenewal_schedules, but what i would like it do to is only have 1 row for each ID, but if it has multiple intervals or templates referencing that ID to put them in the correct column
(picture to explain)..

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 11:43 am
by buckit
so do you want it to return multiple interval columns with each interval value? or 1 interval column with all interval values? either way... its beyond be as to how thats possible using SQL statements.

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 11:46 am
by bla5e
buckit wrote:so do you want it to return multiple interval columns with each interval value? or 1 interval column with all interval values? either way... its beyond be as to how thats possible using SQL statements.
i want multiple intervals & templates per SCHEDULE id (the ID shown on the form in the picture)

Code: Select all

[4 | 3 days | test    | 1 | edit del ]
      15 days   test        

[ 3 | 30 days | test | 0 | edit del ]
        5 days      test       
lol example of table ^^

Re: Related Question, Need help asap [SUBQUERY HELP]

Posted: Wed Jul 14, 2010 12:17 pm
by bla5e
buckit wrote:SELECT * FROM autorenewal_schedules_intervals WHERE templateid = ".the templateid from previous select results." AND scheduleid != ".the scheduleid from previous select results."

the second WHERE statement "scheduleid !=" is just saying WHERE scheduleid does not equal the scheduleid I already have from my previous SELECT.
do you have an example of a nested loop for SQL? in theory i dont think it will work