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?

H
harkusaAuthor
Known Participant
February 3, 2010

Your right. I don't need to use a derived table or 2 recordsets,

It's just the SQL already has an LEFT JOIN and a subquery and adding another subquery is starting to get beyond my knowlege of SQL a bit.


That's why I tried to do it the recordser way.

It's obvious now I'll need to do it in SQL but I don't know how.

To clarify I need to.

  • Look up the offers table
  • Access the advert table using both the TO AND FROM (advert_id's) separately. which are foreign keys to the advert table. I need to do this because I need detailed advert information on the user's advert (to) and the sending user's advert (from).
  • but only for the current user.
  • It needs to be a LEFT outer join as the user's adverts may have no offers.
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