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

I'll take a closer look when I get into the office later. Is there any relationship between advert.username and member.username?


advert does not have a username. it has a user_id, which is a foreign key of member.user_id. A session variable sessionuser is set up in the login program which is a check to see if the advert belongs to the user.

This is not my main concern though, its accesses two separate advert records based on the offer.from and the offer.to.

I've done some research on this and the best advice i've had is to use alias

Select x.*,

     y1.advert_id,

     y2.advert_id

From advert x

LEFT

JOIN offer y1

     ON x.advert_id = y1.from

LEFT

JOIN offer y2

     ON x.advert_id = y2.to

However, this basically does the opposite to what I want. Which is an alias for the advert table

Here's the closest I've got

Select a.*, b.*, offer.*

FROM  advert a, b

LEFT

JOIN offer

     ON a.advert_id = offer.from

LEFT

JOIN offer

     ON b.advert_id = offer.to

SQL, however does not allow the alias on advert. I did try to give offer an alias too but that didn't help


Surely there must be a solution to this.

Maybe I need to go back down the recordset route?

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