Re: [PHP] SQL help?
Skip Evans wrote: Hey all, I have a SQL requirement I'm not quite sure how to compose. I have two tables, shows, and shows_dates. It's a one to many relationship where there is a single entry in shows and multiple entries in shows_dates that list each date and time for a play production for a run of entries in shows, like I need a query that will read each record in shows, but I only want the first record from shows_dates, the first one sorted by date, so I can display all shows in order of their opening date. Not sure how to grab just the first record from shows_dates though. Hint, anyone? Thanks, Skip Join the two tables like you normally would do and aggregate the opening date column with your dbms-specific max function and finally group the result by a distinct value from shows. It would have bee easier if you stated which rdbms you use ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL help
You'll need to do a left/right outer join.
as you didn't say which database you're using, I'll give this back to you to
do some reading up on how to do it...
HTH
Martin
-Original Message-
From: Marc Greenstock [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 11 February 2004 1:53 PM
To: [EMAIL PROTECTED]
Subject: [PHP] SQL help
Hi all here's my problem,
I have two tables: 'user_data' and 'image_data'
obviously user_data has all the user information and
image_data has all the
image information
in image_data are the fields: Image_ID int(11), User_ID int(11) and
Default_Img enum('Yes','No'). Users can have as many images
as they like,
but it is optional.
I want to select from user_data and image_data and get the
results based
upon the rest of the query. The problem is my sql is only
pulling out the
users with images and ignoring the rest
my sql looks like this:
SELECT * FROM user_data, image_data
WHERE user_data.User_ID = image_data.User_ID
AND image_data.Default_Img = 'Yes'
GROUP BY user_data.User_ID
thanks for your help.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL help
The problem is you are using a hard join which will only return rows where
there are matching entries in both tables. What you probably want is called
a left join, however you have a further complication.
You have to have an image row to have a image_data.Default_Img = 'Yes'. So
this query is really designed to only return users who have default images
established (other images don't count either).
Haven't tested this, but it reflects my understanding of your expectation
(haven't double checked syntax either, but you can at
http://www.mysql.com/doc/en/index.html);
SELECT * FROM user_data
left join image_data
on user_data.User_ID = image_data.User_ID
where image_data.Default_Img in ('Yes', '')
GROUP BY user_data.User_ID
hope this works for you, good luck,
Warren Vail
-Original Message-
From: Marc Greenstock [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 6:53 PM
To: [EMAIL PROTECTED]
Subject: [PHP] SQL help
Hi all here's my problem,
I have two tables: 'user_data' and 'image_data'
obviously user_data has all the user information and image_data has all the
image information
in image_data are the fields: Image_ID int(11), User_ID int(11) and
Default_Img enum('Yes','No'). Users can have as many images as they like,
but it is optional.
I want to select from user_data and image_data and get the results based
upon the rest of the query. The problem is my sql is only pulling out the
users with images and ignoring the rest
my sql looks like this:
SELECT * FROM user_data, image_data
WHERE user_data.User_ID = image_data.User_ID
AND image_data.Default_Img = 'Yes'
GROUP BY user_data.User_ID
thanks for your help.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL help
The DB is MySQL 4.0.16,
all tables are MyISAM
Martin Towell [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
You'll need to do a left/right outer join.
as you didn't say which database you're using, I'll give this back to you
to
do some reading up on how to do it...
HTH
Martin
-Original Message-
From: Marc Greenstock [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 11 February 2004 1:53 PM
To: [EMAIL PROTECTED]
Subject: [PHP] SQL help
Hi all here's my problem,
I have two tables: 'user_data' and 'image_data'
obviously user_data has all the user information and
image_data has all the
image information
in image_data are the fields: Image_ID int(11), User_ID int(11) and
Default_Img enum('Yes','No'). Users can have as many images
as they like,
but it is optional.
I want to select from user_data and image_data and get the
results based
upon the rest of the query. The problem is my sql is only
pulling out the
users with images and ignoring the rest
my sql looks like this:
SELECT * FROM user_data, image_data
WHERE user_data.User_ID = image_data.User_ID
AND image_data.Default_Img = 'Yes'
GROUP BY user_data.User_ID
thanks for your help.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] SQL help
Correct me if I'm wrong...PLEASE.. but, I believe you are looking for...
SELECT * FROM user_data left join image_data on
user_data.User_ID=image_data.User_ID where image_data.Default_Img = 'Yes'
GROUP BY user_data.User_ID
I haven't used enum('Yes','No') so I'm taking your word for the fact that
you are restricting your selection to values of 'Yes' works as anticipated.
As long as there is a one to one relationship on user id I believe the left
join works for this.
Larry
-Original Message-
From: Marc Greenstock [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 9:53 PM
To: [EMAIL PROTECTED]
Subject: [PHP] SQL help
Hi all here's my problem,
I have two tables: 'user_data' and 'image_data'
obviously user_data has all the user information and image_data has all the
image information
in image_data are the fields: Image_ID int(11), User_ID int(11) and
Default_Img enum('Yes','No'). Users can have as many images as they like,
but it is optional.
I want to select from user_data and image_data and get the results based
upon the rest of the query. The problem is my sql is only pulling out the
users with images and ignoring the rest
my sql looks like this:
SELECT * FROM user_data, image_data
WHERE user_data.User_ID = image_data.User_ID
AND image_data.Default_Img = 'Yes'
GROUP BY user_data.User_ID
thanks for your help.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] SQL help
That works well,
Thanks all.
Warren Vail [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
The problem is you are using a hard join which will only return rows where
there are matching entries in both tables. What you probably want is
called
a left join, however you have a further complication.
You have to have an image row to have a image_data.Default_Img = 'Yes'.
So
this query is really designed to only return users who have default images
established (other images don't count either).
Haven't tested this, but it reflects my understanding of your expectation
(haven't double checked syntax either, but you can at
http://www.mysql.com/doc/en/index.html);
SELECT * FROM user_data
left join image_data
on user_data.User_ID = image_data.User_ID
where image_data.Default_Img in ('Yes', '')
GROUP BY user_data.User_ID
hope this works for you, good luck,
Warren Vail
-Original Message-
From: Marc Greenstock [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 10, 2004 6:53 PM
To: [EMAIL PROTECTED]
Subject: [PHP] SQL help
Hi all here's my problem,
I have two tables: 'user_data' and 'image_data'
obviously user_data has all the user information and image_data has all
the
image information
in image_data are the fields: Image_ID int(11), User_ID int(11) and
Default_Img enum('Yes','No'). Users can have as many images as they like,
but it is optional.
I want to select from user_data and image_data and get the results based
upon the rest of the query. The problem is my sql is only pulling out the
users with images and ignoring the rest
my sql looks like this:
SELECT * FROM user_data, image_data
WHERE user_data.User_ID = image_data.User_ID
AND image_data.Default_Img = 'Yes'
GROUP BY user_data.User_ID
thanks for your help.
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql help ?
select * from tracker where day='1' and month=' .$month. ' XXX; XXX = but donot return values with the same value in the sess_id field what should XXX be ? one line for each sess_id select * from tracker where day='1' and month=' .$month. ' group by sess_id; or one line for each sess_id with only one row: select * from tracker where count(sess_id)=1 and day='1' and month=' .$month. ' group by sess_id; michi -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] sql help ?
or one line for each sess_id with only one row: select * from tracker where count(sess_id)=1 and day='1' and month=' .$month. ' group by sess_id; sorry that was wrong! correct: select * from tracker where day='1' and month=' .$month. ' group by sess_id having count(sess_id)=1; michi -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
