Making a join seems simple but I can't get it to work

[whereskris]

Hi All,

I have 2 tables LUGallery and LUSubGallery, the tables are related by the GalleyID field

LUGallery

Gallery ID	ClientID	GalleryName
5	50	Australia
8	50	Weddings
12	33	Portraits
4	33	Landscapes

LUSubGallery

SubGalleryID	GalleryID	GalleryName
67	5	NSW
68	5	QLD
69	8	Reception
70	8	Location
87	8	Ceromony
97	4	Rain Forest

What I am try to do seems simple but I can't get it to work the way I want it.

I am trying to write a query to display Galleries from the LUGallery tbl that have a Sub Gallery attached to them (ie. It's GalleryID appears in the LUSubGallery tbl)

So the query for the above example would list Australia, Weddings & Landscapes

I have tried to write code both with inner joins and nested queries but just can't get it right,

Any help would be greatly appreciated.

Thanks in advance

Kris

[-__cfSearching__-]

I have tried to write code both with inner joins and nested

queries but just can't get it right,

Can you post your query?

[whereskris]

I have tried this join

<cfquery name="join" datasource="#Application.dsn#">
SELECT     LUGallery.GalleryID, LUGallery.ClientID, LUGallery.GalleryName, LUGallery.Rating, LUGallery.GalleryImage, LUGallery.GalleryInformation,
                      LUGallery.DisplayOnWebsite, LUGallery.DisplayType, LUGallery.Username, LUGallery.Password, LUGallery.GalleryDateAdded,
                      LUGallery.GalleryDateEdited, LUGallery.DirName, LUGallery.Locked, LUSubGallery.SubGalleryID, LUSubGallery.GalleryID AS Expr1,
                      LUSubGallery.GalleryName AS Expr2, LUSubGallery.Rating AS Expr3, LUSubGallery.GalleryImage AS Expr4,
                      LUSubGallery.GalleryInformation AS Expr5, LUSubGallery.DisplayOnWebsite AS Expr6, LUSubGallery.DisplayType AS Expr7,
                      LUSubGallery.GalleryDateAdded AS Expr8, LUSubGallery.GalleryDateEdited AS Expr9
FROM         LUGallery INNER JOIN
                      LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
</cfquery>

It returns all of the records from both tables that Have the same GalleryID, this is no good to me as I just want to display the GalleryNames from the LUGallery tbl once

Kris

[-__cfSearching__-]

I just want to

display the GalleryNames from the LUGallery tbl once

Then you probably do not want include all the extra columns from the LUSubGallery table.

It returns all of the records from both tables that Have

the same GalleryID

That is the way this type of JOIN works. It will return one record for each matched GalleryID. If you only want to display the unique gallery names you can either:

1) Use an EXISTS clause.  It will return the distinct records from the main gallery table IF a matching record exists in the LUSubGallery table

--- Not tested ---

SELECT  LUGallery.GalleryID,

LUGallery.GalleryName

FROM    LUGallery

WHERE   EXISTS (

        SELECT  *

        FROM LUSubGallery

        WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

        )

http://www.techonthenet.com/sql/exists.php

... OR ....

2) Use the DISTINCT operator to return only the unique combinations of the selected columns. Note: DISTINCT considers all columns in the SELECT list . So if you only want unique galleries, do not include the LUSubGallery columns in the SELECT list.

http://www.w3schools.com/SQl/sql_distinct.asp

Message was edited by: -==cfSearching==-

[Dan_Bracuk]

You can use the group attribute of cfoutput query="yourquery" to get the gallery names to display just once.  You have a problem with your database design though.  Storing the clientid in the gallery table is forcing you to have duplicate records.

[-__cfSearching__-]

Storing the clientid in the gallery table is

forcing you to have duplicate records.

Not necessarily. How are you defining unique gallery names? Unique galleries by client, or just unique names period.

You can use the group attribute of cfoutput

query="yourquery" to get the gallery names to display just

once.

Generally it is better to retrieve only the data needed, rather than grabbing everything, but only outputting a subset of the records. Granted if the resultset is small, performance differences may not be significant.

[whereskris]

Yes you are right I have not got duplicate records As I am displaying galleries by ClientID, Sub galleries are then displayed if the gallery has sub galleries.

This code you posted looks like what I am after

SELECT  LUGallery.GalleryID,

LUGallery.GalleryName

FROM    LUGallery

WHERE   EXISTS (

        SELECT  *

        FROM LUSubGallery

        WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

        )

but I get the following errror.

Error Executing Database Query.

[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'LUSubGallery'.

Thanks for trying to help me.

[-__cfSearching__-]

but I get the following errror.

It looks okay. Though I did not test it. Try running the statement

directly in your database. It usually provides better error messages.

[Adam Cameron.]

but I get the following errror.

   It looks okay. Though I did not test it. Try running the statement

   directly in your database. It usually provides better error messages.

It runs fine for me.

So I don't think that is the entirety of the <cfquery> being run.

Can you post the actual code.

--

Adam

[whereskris]

HI This is the query that is causing the error in coldfusion

<cfquery name="exists" datasource="#Application.dsn#">
SELECT  LUGallery.GalleryID, LUGallery.GalleryName
FROM    LUGallery
WHERE   EXISTS (
        SELECT  *
        FROM LUSubGallery
        WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
        )
        LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
</cfquery>

Also I get this error

Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'LUSubGallery'.

when I run The query

SELECT  LUGallery.GalleryID, LUGallery.GalleryName
FROM    LUGallery
WHERE   EXISTS (
        SELECT  *
        FROM LUSubGallery
        WHERE LUGallery.GalleryID = LUSubGallery.GalleryID
        )
        LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID

In SQL Server Management Studio Express

[-__cfSearching__-]

HI This is the query that is causing the error in

coldfusion

Try running the query you posted earlier. Notice, it does not have the extra "LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID" statement at the end. That is what is causing the error. Remove it and it should work properly.

SELECT LUGallery.GalleryID, LUGallery.GalleryName

FROM LUGallery

WHERE EXISTS (

SELECT *

FROM LUSubGallery

WHERE LUGallery.GalleryID = LUSubGallery.GalleryID

)

-Leigh

[whereskris]

Thanks to everyone for their input I have got a soloution, I'm not sure if it is the best or not but it works and it is a combination of a join and group attribute for cfquery.

<cfquery name="rsGetGalleriesWithSubGalleries" datasource="#Application.dsn#">
SELECT LUGallery.GalleryID, LUGallery.GalleryName
FROM LUGallery
INNER JOIN LUSubGallery ON LUGallery.GalleryID = LUSubGallery.GalleryID
WHERE ClientID = '#Application.ClientID#'
</cfquery>

<cfoutput query="rsGetGalleriesWithSubGalleries" group="GalleryName">
#rsGetGalleriesWithSubGalleries.GalleryName#<br>
</cfoutput>

Thanks for your input.

Kris

[Dan_Bracuk]

That query will only return galleries that have sub-galleries.  This may or may not be what you wanted.

[whereskris]

Yes this is exactly what I was after

I am trying to write a query to display Galleries from the LUGallery tbl that have a Sub Gallery attached to them (ie. It's GalleryID appears in the LUSubGallery tbl)

Maybe I could have said it a bit simpler.

[Guest]

Why even use the EXISTS clause at all? I am pretty sure this only runs a join type query underneath the covers anyway.

Much simplier would be:

select * from LUGallery

where GalleryID in (select GalleryID from LUSubGallery)

You should pretty much only use JOINS if you want columns from both tables, other wise like above just use IN.  Also dont use select * like I have in production code.

Cheers

[-__cfSearching__-]

I believe what you are suggesting is the opposite of what the OP is after ..

[Guest]

I dont see how its the opposite, considering it fits his simplified description of what he is trying to do.  It is also doing the same thing as your last suggested sql statement, just simplier and it should also be faster.

Maybe he will get back to us and let is know.

Cheers

[-__cfSearching__-]

I dont see how its the opposite, considering it fits his simplified description of what he is trying to do. 

My bad. That is what happens when you skim ...

... just simplier

Personally, I do not find it is simpler. Just a slightly different way of writing it.  But yes, both options are perfectly valid.

... and it should also be faster.

Why do you say that?

[Guest]

... and it should also be faster.

Why do you say that?

Experience has shown me that it is faster in cases I have had in the past on various db's.  There are also quite a few blog entries around that discuss the subject; for and against i must admit.

However, I am using MS SQL 2005 at the moment and tried out a demo of the two different query types.  What is interesting, is that the execution plan is exactly the same.  So the query optimiser seems to be doing its job, and it wouldn't matter what way it was done.

I think the performance though of in vs exists will also vary from engine to engine, so people might experience different results because to this.  It is also important to note the exclusion using "not in vs not exists vs left outer join" is another kettle of fish.

Thanks for asking the question though, otherwise i wouldn't of even bothered looking into it again.

Cheers

[-__cfSearching__-]

I think the performance though of in vs exists will also

vary from engine to engine, so people might experience

different results because to this. 

Yes, fair point. How well statements are optimized depends a lot on the database. On average, MS SQL tends to do a pretty good job of divining the intent of similar statements, and reducing them to the same execution plan. But not every database will behave the same. So reviewing the execution plans is probably the best approach.