Related Question, Need help asap [SUBQUERY HELP]

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

Related Question, Need help asap [SUBQUERY HELP]

Post 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']}";
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

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

Post 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
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

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

Post by bla5e »

can anyone help please?! i think i need to use a JOIN?? :crazy:
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

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

Post 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!
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

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

Post 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
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

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

Post 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?
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

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

Post 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)..
Attachments
Untitled.jpg
Untitled.jpg (12.67 KiB) Viewed 11402 times
buckit
Forum Contributor
Posts: 169
Joined: Fri Jan 01, 2010 10:21 am

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

Post 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.
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

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

Post 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 ^^
bla5e
Forum Contributor
Posts: 234
Joined: Tue May 25, 2004 4:28 pm

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

Post 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
Post Reply