2 SELECT statements in one recordset??? php/mysql for search

Report · Jun 23, 2007

I have two tables one name class1 and the other class2 where there is no join possible.
They are for a search result where I also have a conditon that is to be met that they both have a status as being 'active'.
Do I do it as one SELECT DISTINCT gving conditions for one table then the other and
WHERE class1.status = 'active' AND class.2.status = 'active'
Should it be SELECT DISTINCT gving conditions for one table then
SELECT DISTINCT gving conditions for second table.

I am only used to inner. left and right joins.

Hope someone can help, not sure whether you can have two SELECT statements in one recordset

Report · Jun 23, 2007

If there is no relationship between both of these tables I am curious as to
what you are going to search for and what kind of results you are going to
display on the screen.

"jjjhbj111" <webforumsuser@macromedia.com> wrote in message
news:f5im1f$d4$1@forums.macromedia.com...
>I have two tables one name class1 and the other class2 where there is no
>join
> possible.
> They are for a search result where I also have a conditon that is to be
> met
> that they both have a status as being 'active'.
> Do I do it as one SELECT DISTINCT gving conditions for one table then the
> other and
> WHERE class1.status = 'active' AND class.2.status = 'active'
> Should it be SELECT DISTINCT gving conditions for one table then
> SELECT DISTINCT gving conditions for second table.
>
> I am only used to inner. left and right joins.
>
> Hope someone can help, not sure whether you can have two SELECT statements
> in
> one recordset
>

Report · Jun 23, 2007

Also, take a look at this:

Cross Join
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymstcrojo.htm

"jjjhbj111" <webforumsuser@macromedia.com> wrote in message
news:f5im1f$d4$1@forums.macromedia.com...
>I have two tables one name class1 and the other class2 where there is no
>join
> possible.
> They are for a search result where I also have a conditon that is to be
> met
> that they both have a status as being 'active'.
> Do I do it as one SELECT DISTINCT gving conditions for one table then the
> other and
> WHERE class1.status = 'active' AND class.2.status = 'active'
> Should it be SELECT DISTINCT gving conditions for one table then
> SELECT DISTINCT gving conditions for second table.
>
> I am only used to inner. left and right joins.
>
> Hope someone can help, not sure whether you can have two SELECT statements
> in
> one recordset
>

Report · Jun 23, 2007

If my understanding of what you are wanting to produce is correct, you could
use 2 derived tables and then reference them in one select. You will get the
general concept from the following:

SELECT * FROM
(
SELECT * FROM Class1
WHERE Status = 'Active'
) a,
(
SELECT * FROM Class2
WHERE Status = 'Active'
) b

"jjjhbj111" <webforumsuser@macromedia.com> wrote in message
news:f5im1f$d4$1@forums.macromedia.com...
>I have two tables one name class1 and the other class2 where there is no
>join
> possible.
> They are for a search result where I also have a conditon that is to be
> met
> that they both have a status as being 'active'.
> Do I do it as one SELECT DISTINCT gving conditions for one table then the
> other and
> WHERE class1.status = 'active' AND class.2.status = 'active'
> Should it be SELECT DISTINCT gving conditions for one table then
> SELECT DISTINCT gving conditions for second table.
>
> I am only used to inner. left and right joins.
>
> Hope someone can help, not sure whether you can have two SELECT statements
> in
> one recordset
>

Report · Jun 23, 2007

Hmmm..........Be very careful with Cross Joins. They can be the work of the
SQL Devil himself!

"Pizza Good" <PizzaGood@earthlink.com> wrote in message
news:f5j2u9$dbu$1@forums.macromedia.com...
> Also, take a look at this:
>
> Cross Join
> http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymstcrojo.htm
>
>
>
> "jjjhbj111" <webforumsuser@macromedia.com> wrote in message
> news:f5im1f$d4$1@forums.macromedia.com...
>>I have two tables one name class1 and the other class2 where there is no
>>join
>> possible.
>> They are for a search result where I also have a conditon that is to be
>> met
>> that they both have a status as being 'active'.
>> Do I do it as one SELECT DISTINCT gving conditions for one table then the
>> other and
>> WHERE class1.status = 'active' AND class.2.status = 'active'
>> Should it be SELECT DISTINCT gving conditions for one table then
>> SELECT DISTINCT gving conditions for second table.
>>
>> I am only used to inner. left and right joins.
>>
>> Hope someone can help, not sure whether you can have two SELECT
>> statements in
>> one recordset
>>
>
>

Report · Jun 23, 2007

"SQL Devil himself"....that's a good one. :-)

But like I always say...there is a time and place for just about everything.

"Pat Shaw" <pat@nomail.com> wrote in message
news:f5j3lk$e23$1@forums.macromedia.com...
> Hmmm..........Be very careful with Cross Joins. They can be the work of
> the SQL Devil himself!
>
>
> "Pizza Good" <PizzaGood@earthlink.com> wrote in message
> news:f5j2u9$dbu$1@forums.macromedia.com...
>> Also, take a look at this:
>>
>> Cross Join
>> http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/sqlp/rbafymstcrojo.htm
>>
>>
>>
>> "jjjhbj111" <webforumsuser@macromedia.com> wrote in message
>> news:f5im1f$d4$1@forums.macromedia.com...
>>>I have two tables one name class1 and the other class2 where there is no
>>>join
>>> possible.
>>> They are for a search result where I also have a conditon that is to be
>>> met
>>> that they both have a status as being 'active'.
>>> Do I do it as one SELECT DISTINCT gving conditions for one table then
>>> the
>>> other and
>>> WHERE class1.status = 'active' AND class.2.status = 'active'
>>> Should it be SELECT DISTINCT gving conditions for one table then
>>> SELECT DISTINCT gving conditions for second table.
>>>
>>> I am only used to inner. left and right joins.
>>>
>>> Hope someone can help, not sure whether you can have two SELECT
>>> statements in
>>> one recordset
>>>
>>
>>
>
>

Report · Jun 24, 2007

Hi thanks for the replies........appreciated.
I have two tables class1 - free listings; class2 - paid listings with extra options and shown differently on the results page, a little like google search - the paid adverts are shown differently on the results page.
This is why I have two separate tables in mysql.
A person having a paid listing can also have a free listing, but the free listing can not show in the paid listing space......
Hope that makes sense. I thought about having only one table and having paid or not paid, maybe that would be better.

Love to hear your views...........thanks............

Report · Jun 24, 2007

It would be a million times better, easier and more logical to have everyone
in one table and just have a field flag them as paid or free. That is
definitely the way to do it. Trust me...it will make your programming
experience a much more pleasant one.

"jjjhbj111" <webforumsuser@macromedia.com> wrote in message
news:f5l8in$re3$1@forums.macromedia.com...
> Hi thanks for the replies........appreciated.
> I have two tables class1 - free listings; class2 - paid listings with
> extra
> options and shown differently on the results page, a little like google
> search
> - the paid adverts are shown differently on the results page.
> This is why I have two separate tables in mysql.
> A person having a paid listing can also have a free listing, but the free
> listing can not show in the paid listing space......
> Hope that makes sense. I thought about having only one table and having
> paid
> or not paid, maybe that would be better.
>
> Love to hear your views...........thanks............
>
>

Report · Jun 24, 2007

.oO(jjjhbj111)

> I have two tables class1 - free listings; class2 - paid listings with extra
>options and shown differently on the results page, a little like google search
>- the paid adverts are shown differently on the results page.
> This is why I have two separate tables in mysql.

Most likely I would have done that with a single table 'listings', with
one column stating whether it's a paid or a free listing. For the
additional options I probably would have used a second table. When
necessary both can be joined with a LEFT JOIN. But without knowing the
details that's just guessing.

> A person having a paid listing can also have a free listing, but the free
>listing can not show in the paid listing space......
> Hope that makes sense. I thought about having only one table and having paid
>or not paid, maybe that would be better.
>
> Love to hear your views...........thanks............

In fact a VIEW could be helpful, MySQL supports them as of v5.0. ;)

But what about a UNION to simply combine the results of two or more
SELECT statements into one?

Micha

Report · Jun 26, 2007

Thank you so much guys,
what I will do is make it one table with a column of paid or not paid and another column with status active or status inactive.
Yes that will save so much in coding....
Thanks again.....

Report · Jul 03, 2007

OK guys, just a quick question regarding the showing results.
If it is the one table and I want the listings with the status is 'active' only showing, but also to have the 'paid' showing first on the list of repeats.
Is this done by order by??? I figure group by will only show one type of listing.
Thanks again........

Report · Jul 11, 2007

add "both" to paid or free as options.

Report · Jul 12, 2007

OK
I went and changed it to two tables because for some reason I would get both type of listings showing when placed active.
e.g. On the results page it would show the free listings twice, once where it should be and then where only the paid options should be. I fixed that by removing the WHERE status = 'active' AND paystatus = 'paid' from the paid listings to only show WHERE paystatus = 'paid' because the AND wasn't working for me. I am not sure whether it was because of using %Search% in the recordset??
Once fixed then I started out on my admin pages and realised it would be much simpler for me to have a paid table and a free table. This way I can check separately without hiccups. The main reason I was thinking one table would be good is when a user wanted to upgrade to a paid listing, they would be able to fill in the extra fields in the form required.
But because in the free listings I check that only one email address and only one business name is used - so they only get one listing per business, I would run into trouble with the upgrade.
I am going to allow them to leave their free listing and simply fill out a new form for the paid listing.
The only trouble I think I will run into is when I want to delete the business, that is when I will need to have a JOIN.
Please I have only used INNERJOIN, what difference will a left join do. Why not a right join?
Thanks heaps for all your help.