Skip to main content
H
harkusa
Known Participant
February 2, 2010
Question

SQL, recordsets and multiple table queries.

  • February 2, 2010
  • 3 replies
  • 2317 views

I have a table and a recordset containing the following SQL. which displays a members adverts if they have any offers or not. This works fine.

SELECT * FROM (

SELECT * FROM  advert LEFT JOIN offer ON advert.advert_id = offer.to)

a WHERE a.user_id = (SELECT members.user_id from members where members.username = sessionuser)

Basically the offers table contains an offers.from and an offers.to foreign key to the adverts parent table.

I can display the offers.to foreign key and link it to the advert table fine.

Now the problem I have is I need to link the offer.from foreign key to the advert table.

I created a 2nd recordset using a URL parameter offers.from. to access the adverts table again for the information (on a different record)

Although this works using the 'test' feature in the SQL dialogue box (in which the offer.from parameter is entered), when I run it nothing is displayed. I have even displayed offer.from in the table to make sure it is getting that value, which it is.

Is this because..........

                             I have chosen to display only records for the the users particular login in the 1st recordset.

                            recordset1 and recordset2 are not linked?

Any other suggestions.

This topic has been closed for replies.

3 replies

H
harkusaAuthor
Known Participant
February 2, 2010

I need an offers table because it contains information on the transaction between the two adverts.

E.g. status (open, complete etc) and date.

B
bregent
Participating Frequently
February 3, 2010

>I need an offers table because it contains

>information on the transaction between the two adverts.

Yes, I understand that. My questions were:

1) Why are you complicating things by using a derived table?

2) Why do you need two recordsets? Why not just pull all the data into a single recordset?

B
bregent
Participating Frequently
February 5, 2010

Success !!!!!

Thanks so much.

I needed to tinker with the code a bit, basically it wouldn't work without the aliases x , y and z on the aliased table (advert_1) elements.

I also added the functionality to only display a logged in user's own adverts.

SELECT  advert.advert_id, advert.o_make,  advert.o_model, advert.w_make, advert.w_model, offer.offer_id, offer.from, offer.to, advert_1.advert_id x, advert_1.o_model y, advert_1.o_make z

FROM (advert LEFT JOIN offer ON advert,advert_id = offer.to)

INNER JOIN members m

ON advert.user_id = m.user_id AND m.username = sessionuser

LEFT JOIN advert AS advert_1 ON offer.from = advert_1.advert_id

Here's the results, attached.

Hope this helps anyone else with a similar problem.

I can get on with the rest of my project now

Thanks again.


Great. To filter by user you can do it the way you currently have it - joining the members table, or do it the way your had it in your original post - using a subquery. Whichever is more readable for you.

And yes, you do need to alias the column names if they are listed more than once, as Dreamweaver doesn't seem to be able to correctly resolve fully qualified names in server behaviors.

B
bregent
Participating Frequently
February 2, 2010

>I can display the offers.to foreign key and link it to the advert table fine.

>Now the problem I have is I need to link the offer.from foreign key to the advert table.

>I created a 2nd recordset using a URL parameter offers.from. to access the adverts table again for the information (on a different record)

Why do you need to create a 2nd recordset. Can't you just create a table alias for advert and join to the offers.from column?  Or do you need two recordsets for another reason?

Also, why are you using a derived table? I don't see a reason for it.

H
harkusaAuthor
Known Participant
February 2, 2010

To be honest I did try to do it like this but couldn't then select the binded objects in dreamweaver as the syntax was incorrect.

Is it possibe to have an INNER JOIN with an alias anyway?

In any case It would be better to go the SQL route.

Any suggestion on how the code would look?

B
bregent
Participating Frequently
February 3, 2010

>To be honest I did try to do it like this but

>couldn't then select the binded objects in

>dreamweaver as the syntax was incorrect.

OK, I'm not exactly sure what you tried, but one of your problems could be due to your use of Select *.  It's almost always better to explicitly state the list of columns you want. This is extemely important if you have two tables that share column names as they will be ambiguous. And if you are aliasing a second adverts table as I suggested, then you are guaranteed to have ambiguous columns so you need to list the columns explicitly.

H
harkusaAuthor
Known Participant
February 2, 2010

Well, it not a problem with the user login as I have changed the offers to be for the same user,just to eliminate this possibility

So it must be to do with the actual recordset

Any Suggestions

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices