How to establish join precedence?

Howdy folks, I have a pesky little logic error that seems to be eluding me that I believe is related to a join precedence error. I have a fairly complex schema to work with, but for examples sake I'll just say three tables.

ag : ag_num, ag_fname, ag_lname
qt : qt_num, ag_num
pl : pl_num, qt_num

Now I want to retrieve all of the records from ag and return null for qt.* and pl.*. For some reason this is eluding me, my train of thought is as follows:

SELECT ag.*, qt.*, pl.*
FROM ag LEFT OUTER JOIN qt ON ag.ag_num=qt.ag_num
INNER JOIN pl.qt_num=qt.qt_num

I know that the error is with the INNER JOIN between pl and qt, but I cannot figure out how to properly define that relationship. For some reason it's just not clicking for me.

The last time I ran into this issue was with returning a single record based on criteria in the where clause. Essentially the record was returning nothing, as opposed to null, because I was only filtering a dataset. I discovered that if you filter the dataset instead of defining it in the join statement, you will not return null. I solved that by moving the criteria into the join statement thereby redefining the base dataset.

That wont work for this particular situation. I need to be able to use qt as an intermediary to pl without having it exclude records. What's the basic hook that I'm missing here?If you will be using the reults returned from the set of QT and PL then a view would be convenient.

select *
from ag LEFT OUTER JOIN
(select qt.ag_num from qt INNER JOIN pt ON qt.qt_num = pl.qt_num) IJ
ON ag.ag_num = IJ.ag_num

I have only selected qt.ag_num from the result set given by QT joined on PL, as I am not sure of what else you wanted to display. Note however that you cannot use select *, as both qt and pl contain duplicate colum headers, if you need to display all columns from qt, pl then you can rename the duplicates.

By doing the OUTER JOIN followed by an INNER JOIN without brackets to specify precedence you are essentially just doing an inner join. The outer join returns a set with non matching tuples included in the set with Null values to represent the other table's values. This set is then compared with the third table using a standard inner join, as a result the tuples with nulls will be disregarded hence an inner join between table1, table2 and table3.|||Thanks for the reply!

Unfortunately, I receive a syntax error when attempting your method. I am using SQL Server 2k, I received "ERROR NEAR INNER JOIN". However, your reply gave me a good tip to go on that allowed me to solve it myself! I am most appreciative.

I figured out that the error in my logic was I assumed the join statement was executed left to right as far as precedence is concerned. However, you pointed out that for this scenario, the join really needs to be on the end of the statement. by rephrasing my query to:

SELECT ag_num
FROM pl INNER JOIN qt ON pl.qt_num=qt.qt_num
RIGHT OUTER JOIN ag ON ag.ag_num=qt.ag_num

I have essentially defined the bracketing you mentioned. As I needed to first define the inner join between pl and qt, and THEN compare it to ag.

... it's always the simple stuff that gets ya.

:)sql

How to do this

Hi All,
I have a table Called MailList with 2 columns namely MailId and Message.
There can be many messages related to one email id in the table. I need to
consolidate all the messages into a single message and send the mail to that
id.
(It may or may not exceed the limit of 8000 chars).
For Example,
MailId Message
a@.x.com XXXXXXXXXXXXXXXXXXXXXX
a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
c@.z.com BB
a@.x.com CCC
I need to concatenate the messages of the first, second and fifth record
into one single message and send the mail to a@.x.com. Similarly for the othe
r
two.
How can I do this. Please help me at the earlist.
Sevugan.CYou have to do this in a loop. Wheter you do it in a temporary table or
within a cursor belongs to you coding style.
HTH, Jens Suessmeyer.|||use northwind
-- your problem looks like this --
select supplierid, productname
from products
-- your problem looks like this --
-- solutions--
create proc concatenate
(
@.supplierid Varchar(10)
)
as
select IDENTITY(int, 1,1) AS ID_Num,
Productname into #tempx from products where
supplierid=@.supplierid
declare @.ctr int
declare @.concatenated varchar(5000)
declare @.maxrecord int
select @.maxrecord=max(id_num) from #tempx
select @.concatenated=''
select @.ctr=0
while @.ctr <> @.maxrecord
begin
Select @.ctr=@.ctr+1
Select @.concatenated =@.concatenated+Productname
from #tempx where id_num=@.ctr
end
select @.concatenated
--test solution --
exec concatenate 7
try to improve from there...
thanks,
joeydj
"Sevugan" wrote:

> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to th
at
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the ot
her
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||Which is the PK? You want message to be XXX...YYYY...CCC or
CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
helpful for us.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
> Hi All,
> I have a table Called MailList with 2 columns namely MailId and Message.
> There can be many messages related to one email id in the table. I need to
> consolidate all the messages into a single message and send the mail to
> that
> id.
> (It may or may not exceed the limit of 8000 chars).
> For Example,
> MailId Message
> a@.x.com XXXXXXXXXXXXXXXXXXXXXX
> a@.x.com YYYYYYYYYYYYYYYYYYYYYYYYY
> b@.y.com AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
> c@.z.com BB
> a@.x.com CCC
> I need to concatenate the messages of the first, second and fifth record
> into one single message and send the mail to a@.x.com. Similarly for the
> other
> two.
> How can I do this. Please help me at the earlist.
> --
> Sevugan.C|||It is a temporary table. The Message is the primary key.
I want the message to be xxx...yyy...ccc...
--
Sevugan.C
"Perayu" wrote:

> Which is the PK? You want message to be XXX...YYYY...CCC or
> CCCXXX...YYY....' If youcan post your DDL fo rthe table, it will be
> helpful for us.
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:DAF1AC53-008E-493A-93B0-A82F557E49FD@.microsoft.com...
>
>|||Hi
My problem here is I need to store more than 8000 characters which is not
permitted in VARCHAR variable.
If the no.of records in the table are more than 8000 chars how can I store
that?
--
Sevugan.C
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> use northwind
> -- your problem looks like this --
> select supplierid, productname
> from products
> -- your problem looks like this --
> -- solutions--
> create proc concatenate
> (
> @.supplierid Varchar(10)
> )
> as
> select IDENTITY(int, 1,1) AS ID_Num,
> Productname into #tempx from products where
> supplierid=@.supplierid
> declare @.ctr int
> declare @.concatenated varchar(5000)
> declare @.maxrecord int
> select @.maxrecord=max(id_num) from #tempx
> select @.concatenated=''
> select @.ctr=0
>
> while @.ctr <> @.maxrecord
> begin
> Select @.ctr=@.ctr+1
> Select @.concatenated =@.concatenated+Productname
> from #tempx where id_num=@.ctr
> end
> select @.concatenated
> --test solution --
> exec concatenate 7
>
> try to improve from there...
> --
> thanks,
> --
> joeydj
>
> "Sevugan" wrote:
>|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

>From your vague narrative, you need a column that gives you the message
order.
Finally, you need to learn RDBMS and *basic* tiered architecutre
concepts. This is a display issue and shjould be done in the front
end, never in the database.|||Why can't you use Text column?
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
> Hi
> My problem here is I need to store more than 8000 characters which is not
> permitted in VARCHAR variable.
> If the no.of records in the table are more than 8000 chars how can I store
> that?
> --
> Sevugan.C
>
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>|||If message is the PK, so you have no control which one will be the first
one.
Perayu
"Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
news:B76CCD95-E2F9-4315-BA77-D2B9C614CE76@.microsoft.com...
> It is a temporary table. The Message is the primary key.
> I want the message to be xxx...yyy...ccc...
> --
> Sevugan.C
>
> "Perayu" wrote:
>|||How would I pick the value from that text column to make use of that in the
xp_sendmail procedure call.
Exec Master..xp_sendmail @.message = <how to bring the value of the text
column here>
Sevugan.C
"Perayu" wrote:

> Why can't you use Text column?
> Perayu
> "Sevugan" <Sevugan@.discussions.microsoft.com> wrote in message
> news:7DC75F76-DC08-488A-81DB-90B676261903@.microsoft.com...
>
>